February 1, 2011 at 7:21 pm
Okay, here's a dumb question that I can't believe I haven't been able to figure out on my own...
How do I search a column for a text string that contains an underscore?
column1
3327
8343_1
4362
2155_2
select *
from tablename
where column1 like '%_%'
I want this to return the 8343_1 & 2155_2. But it is using the underscore as a wildcard.
Can someone please provide the correct syntax?
Thank you.
February 1, 2011 at 7:48 pm
Try this:
select column1
from
( select '8343_1'
union all select '3327'
union all select '4362'
union all select '2155_2' ) tablename (column1)
where column1 like '%[_]%'
Enclose your _ with [ and ] to make _ an wildcard in your search, if you are using LIKE operator.
Else u could use CHARINDEX:
select column1
from
( select '8343_1'
union all select '3327'
union all select '4362'
union all select '2155_2' ) tablename (column1)
where charindex('_' ,column1 ) > 0
The disadvantage of this is, CHARINDEX might not use any indexes that are present over Column1.
Else, u can use PATINDEX:
select column1
from
( select '8343_1'
union all select '3327'
union all select '4362'
union all select '2155_2' ) tablename (column1)
where patindex('%[_]%' ,column1 ) > 0
Again PATINDEX is unlikely to use any indexes, if i am not wrong..
Hope this helps!
February 1, 2011 at 8:07 pm
Yes, that is perfect. Thank you!
February 1, 2011 at 10:49 pm
Or escape the character
select column1
from
( select '8343_1'
union all select '3327'
union all select '4362'
union all select '2155_2' ) tablename (column1)
where column1 like '%/_%' ESCAPE '/'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply