allowing ? in TSQL equal to _ - 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 TSQL equal to _

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2012-11-09 : 10:39:41

Hi All:

I have a TSQL stored procedure like this:

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

AS
BEGIN

IF CHARINDEX('*', @SearchText) = 0
SET @SearchText = '%' + @SearchText + '%'
ELSE
SET @SearchText = REPLACE(@SearchText, '*', '%')

SELECT 'Entity' as pagetype,EntityID,EntityData,EntityDataID
FROM EntityData
WHERE EntityData LIKE @SearchText
union all
SELECT 'Property' as pagetype,PropertyID,PropertyValue,EntityDataID
FROM EntityDataProperty
WHERE PropertyValue LIKE @SearchText
END

I want to allow '?' in it which will be equal to '_' operator. How can I allow '?' so that if user types t_ then 'to' is returned. If user types _t then 'It' is returned.

Please suggest me solution to this:

Regards,
Asif Hameed

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-09 : 14:50:39

Not sure I understand your request. As you seemed to indicate the underscore <_> can be used with LIKE to wildcard any single character. You already have code to replace a star <*> with a percent <%>. So can't you do the same thing when a user enters a question mark <?> replace it with an underscore <_> ?

SET @SearchText = replace(REPLACE(@SearchText, '*', '%'),'?','_')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources