SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 allowing ? in TSQL equal to _
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparrow37
Posting Yak Master

147 Posts

Posted - 11/09/2012 :  10:39:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5924 Posts

Posted - 11/09/2012 :  14:50:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000