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
 Old Forums
 CLOSED - General SQL Server
 Escape characters in LIKE
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/14/2006 :  19:56:13  Show Profile
I need to write a query, which returns all records from Test table, which have the Name field containing string "[A_1]" (without the quotation marks).

So, the query should look something like this:
SELECT *
FROM Test
WHERE Name LIKE '[A_1]'

Well, this query doesn't work because it has special characters '[', ']', and '_', which LIKE treats as regular expression patterns.

I also tried the following:
SELECT *
FROM Test
WHERE Name LIKE '![A!_1!]' ESCAPE '!'

But this returns no records at all even though I have row with Name value [A_1].

What is the right way to write this query?

Thanks.

Alec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/14/2006 :  20:12:29  Show Profile
This works OK for me.

select
	*
from
	(select name = '[A_1]' ) Test
where
	name like '![A!_1!]' escape '!'

Results:

name  
----- 
[A_1]

(1 row(s) affected)




CODO ERGO SUM
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/14/2006 :  21:01:49  Show Profile
I am not sure if this matters, but I am on SQL Server 2005.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/14/2006 :  21:38:26  Show Profile
quote:
Originally posted by lebedev

I am not sure if this matters, but I am on SQL Server 2005.



So what happened when you tried the code I posted?



CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/15/2006 :  04:03:15  Show Profile
An alternate way would be

SELECT *
FROM Test
WHERE Name LIKE '[[]A[_]1]'

Personally I prefer the ESCAPE method, but if you have some code like:

SELECT *
FROM Test
WHERE Name LIKE @strFind

and @strFind comes from an application, then using [x] as the escaping tool can be useful as a data entry trick in the application itself.

Kristen
Go to Top of Page

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/15/2006 :  11:54:01  Show Profile
Your examples seemed to work just fine. My query is a little more complex than what I posted, so I think the problem lies somewhere else. Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000