• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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





0
mani_sai
Asked:
mani_sai
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @x   varchar(10)
declare @len int
declare @SQL nvarchar(4000)


SET @x = 'MANNY'
SET @len = LEN(@x)
SET @sql =''

WHILE @len >0
BEGIN
   SELECT @sql =@sql +  ' Select FirstName from Authors Where FirstName like '''+ LEFT(@x,@len)+'%'''
   SELECT @len = @Len -1
   IF @len > 0
        SET @sql = @sql + '  UNION '
END

print @sql
exec sp_executeSQL @Sql
0
 
adwisemanCommented:
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)
0
 
adwisemanCommented:
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
0
 
mani_saiAuthor Commented:
Thanks, It works.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now