Re: Reverse pattern matching]

#1

"Gary Greenberg" wrote:

Quote:

I encountered an unusual problem and wonder if anyone had
some similar experience. I have a table T where the column
contains text values with wild cards and a query contain
exact value. For example
------------------------
| A | B |
------------------------
|AB% | 1 |
|X% | 2 |
|XY% | 3 |
|% | 4 |
------------------------

I'd like to make a query "select B from T where A like 'XYZ'"
I need to get a result sorted by maximum matching. For the
liset case it should be:
3
2
4

Any help will be greatly appreciated.

I don't know how well it will perform, but you can just reverse the operands
to the LIKE operator...

create table #T (
A varchar(10) not null primary key,
B int not null
)
go

insert #T values ('AB%', 1)
insert #T values ('X%', 2)
insert #T values ('XY%', 3)
insert #T values ('%', 4)
go

select B from #T where 'XYZ' like A
go

drop table #T
go

Craig

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Reverse pattern matching]

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Reverse pattern matching] - 05-17-2005 , 07:51 PM