SQL Server Forums - Escape characters in LIKE

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

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

Topic

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/14/2006 : 19:56:13

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
6997 Posts

Posted - 09/14/2006 : 20:12:29

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

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/14/2006 : 21:01:49

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts

Posted - 09/14/2006 : 21:38:26

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

Kristen
Test

United Kingdom
22191 Posts

Posted - 09/15/2006 : 04:03:15

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

lebedev
Posting Yak Master

USA
126 Posts

Posted - 09/15/2006 : 11:54:01

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.

Topic

Forum Locked

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.