Problems in Search opertion using Like

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 DannaniTrap 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.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> 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
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a spec
ial 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.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based o
n
> 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
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> 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 _.
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
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on|||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.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
>
have[vbcol=seagreen]
> 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
>
based[vbcol=seagreen]
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> 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.
>sql