asked on

SQL Wild Card Search

Hello Experts,

Is this something possible in SQL?

Select FirstName from Authors Where FirstName like 'MANNY%'

Right now i am getting:

MANNY
MANNYXXX

but what i want is when the user types 'MANNY"

I want the query to pull the letters starting with

MANNYXXX, MANNY, MAN, MA, M etc....

Thanks

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adwiseman

If you want to return everyrecord that matches 'M%" then
Select *
from Authors
Where FirstName like 'M%'

But I think you getting at, you want to return all those matches, plus sort them in order of how close of a match they are. This generates the dynamic SQL do do that

DECLARE @Filter VARCHAR(30)
SET @Filter = 'MANNY'
DECLARE @Script VARCHAR(4000)
DECLARE @Len INT

SET @Len = LEN(@Filter)

SET @Script = 'Select FirstName, CASE WHEN FirstName LIKE ''' + @Filter + '%'' THEN ' + CONVERT(VARCHAR, @Len) + CHAR(13)

WHILE @Len > 0
BEGIN
SET @Len =@Len - 1

SET @Script = @Script + 'WHEN FirstName LIKE ''' + LEFT(@Filter, @Len) + '%'' THEN ' + CONVERT(VARCHAR, @Len) + CHAR(13)

END

SET @Script = @Script + 'ELSE NULL END AS MyOrderField
from Authors
Where FirstName like ''' + LEFT(@Filter, 1) + '%''
ORDER BY MyOrderField desc'
print @script
exec(@script)

adwiseman

CHANGE 1 line above

WHILE @Len > 0
SHOULD BE
WHILE @Len > 1

That produces this SQL

Select FirstName, CASE WHEN FirstName LIKE 'MANNY%' THEN 5
WHEN FirstName LIKE 'MANN%' THEN 4
WHEN FirstName LIKE 'MAN%' THEN 3
WHEN FirstName LIKE 'MA%' THEN 2
WHEN FirstName LIKE 'M%' THEN 1
ELSE NULL END AS MyOrderField
from Authors
Where FirstName like 'M%'
ORDER BY MyOrderField desc

Mani Pazhana

ASKER

Thanks, It works.