Allowing * in SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Allowing * in SQL query

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2012-11-08 : 07:57:32

I have a stored procedure and I want to allow * in it so that if user:

types t* then taller, tea returns
types * or . then all results are returned
types t then all results which have t are returned like ptv, tall, sit

my stored procedure is this:

ALTER PROCEDURE [dbo].[SearchEntityDataNew]
@SearchText varchar(100) = '*'

AS
BEGIN
SELECT 'Entity' as pagetype,EntityID,EntityData,EntityDataID
FROM EntityData
WHERE EntityData LIKE '%' + @SearchText+ '%'
OR @SearchText = '*'

union all

SELECT 'Property' as pagetype,PropertyID,PropertyValue,EntityDataID
FROM EntityDataProperty
WHERE PropertyValue LIKE '%' + @SearchText+ '%'
OR @SearchText = '*'
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 08:35:43

Is that not working as you expect it to? Seems like the search condition can be

WHERE EntityData LIKE
CASE 
     WHEN @SearchText NOT LIKE '%*%' THEN '%' + REPLACE(@SearchText , '*', '%') + '%'
     ELSE REPLACE(@SearchText , '*', '%')
END

nathansneely
Starting Member

2 Posts

Posted - 2012-11-08 : 09:13:36

Maybe something like this would work.. Although sunitabeck's response looks simpler..

ALTER PROCEDURE test.[SearchEntityDataNew]
@SearchText varchar(100)

AS
BEGIN
IF @SearchText = '*' OR @SearchText = '.'
BEGIN
SELECT myfield FROM mytable
END
IF RIGHT(@SearchText ,1) = '*'
BEGIN
SELECT myfield FROM mytable WHERE myfield LIKE LEFT(@SearchText, LEN(@SearchText) - 1) + '%'
END
IF RIGHT(@SearchText ,1) <> '*'
BEGIN
SELECT myfield FROM mytable WHERE myfield LIKE '%' + SearchText + '%'
END
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources