Problems in Search opertion using Like

Problems in Search opertion using Like

Post by Prasad Dan » Thu, 18 Aug 2005 16:55:07


Hi,

In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,

We have to perform a like search on this field.

If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.

Is there any way solve the problems like this.

Thanks & Regards,

Prasad Dannani
 
 
 

Problems in Search opertion using Like

Post by Hilary Cot » Thu, 18 Aug 2005 18:22:21

Trap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.yqcomputer.com/

Looking for a FAQ on Indexing Services/SQL FTS
http://www.yqcomputer.com/


on

 
 
 

Problems in Search opertion using Like

Post by Tibor Kara » Thu, 18 Aug 2005 18:43:20

Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":

SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'


--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
Blog: http://www.yqcomputer.com/
 
 
 

Problems in Search opertion using Like

Post by Daniel Cri » Thu, 18 Aug 2005 19:26:27

Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:


How about using the ESCAPE keyword or bracketing? eg.

SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'

SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'

Those will look for the string 45% rather than all strings starting with 45.

That way you don't have to mess around with token replacement.

Dan
 
 
 

Problems in Search opertion using Like

Post by Prasad Dan » Fri, 19 Aug 2005 16:27:14

Hi Dan,

Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.

So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.

In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.

Once Again Thanks for Helping Us,
Prasad Dannani.




have
45.


based
 
 
 

Problems in Search opertion using Like

Post by Tibor Kara » Fri, 19 Aug 2005 16:45:34

Did you read my post? Read it and you see how you escape a LIKE clause.

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
Blog: http://www.yqcomputer.com/