Escape characters in LIKE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Escape characters in LIKE

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2006-09-14 : 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)

7020 Posts

Posted - 2006-09-14 : 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

126 Posts

Posted - 2006-09-14 : 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)

7020 Posts

Posted - 2006-09-14 : 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

22859 Posts

Posted - 2006-09-15 : 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

126 Posts

Posted - 2006-09-15 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources