[Fwd: Reverse pattern matching]

[Gary Greenberg]

[Craig Kelly]

"Gary Greenberg" wrote:

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

[Hugo Kornelis]

On Tue, 17 May 2005 17:26:44 -0700, Gary Greenberg wrote:

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.

Hi Gary,

Try this one:

SELECT B
FROM YourTable
WHERE 'XYZ' LIKE A
ORDER BY LEN(A) DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)