January 23, 2012 at 8:22 am
Hi,
I want to find all records where a field contains the following literal text
[SS]
the above four characters can be anywhere in the string. Can anyone advise how to escape from the typical behaviour of square brackets as wildcards. I've tried various ways of using the ! escape character, and google/BOL to no avail.
Any help greatly appreciated.
January 23, 2012 at 8:27 am
DECLARE @t TABLE (Data VARCHAR(30))
INSERT @t (Data)
VALUES ('abcssdef'),
('abc[ss]def')
select * from @t
where data like '%[[ss]]%'
January 23, 2012 at 8:36 am
Thanks for the reply, but that doesn't quite work. Try this, and you'll see what I mean:
create table #t (Data VARCHAR(30))
INSERT #t (Data)
VALUES ('abcssdef'),
('abc[ss]def'),
('abc[70ps]def')
select * from #t
where data like '%[[ss]]%'
January 23, 2012 at 8:41 am
Shoot, you are right, sorry about that. How about this?
DECLARE @t TABLE (Data VARCHAR(30))
INSERT @t (Data) VALUES
('abcssdef'),
('abc[ss]def'),
('abc[70ps]def')
select * from @t
where data like '%\[ss\]%' escape '\'
January 23, 2012 at 8:55 am
That's it! thanks.
I've had to shuttle the data into tempdb on one of our 2005 servers though to get this to work.
Do you know if this is even possible on SS2000 without using ESCAPE?
Regards . . .Jason
January 23, 2012 at 9:06 am
Boy, it is too early in the morning for me. I didn't even realize this was the 2000 board. D'oh. I don't know of anything off the top of my head that will get that without the escape clause so I will wait and see if someone else can chime in on that before I can come up with something.
January 23, 2012 at 9:09 am
Wait, try this in the where clause:
where data like '%[[]ss]%'
January 23, 2012 at 9:11 am
That's the one!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply