[Fwd: Reverse pattern matching]

[Fwd: Reverse pattern matching]"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|||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)