April 9, 2009 at 12:50 pm
Hi All,
I want to write a query to display all the row which contains an (_) underscore.
Like operator does not work as, it identifies _ as an wild char. How to makw SQl understnad that _ here is not a wild char, its just a char. 🙂
Thanks
Dev
April 9, 2009 at 12:54 pm
WHERE SomeColumn LIKE '%/_%' ESCAPE '/'
The escape character makes SQL treat whatever follows it as a literal character.
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
April 9, 2009 at 12:54 pm
Put square-brackets around the underscore in the Like statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 12:58 pm
Wow so quick...thanks Guys that helps. Here is another wasy to do that
SELECT * FROM Table1 WHERE CHARINDEX(@SearchParam , Col1) > 0
Thanks Again..:)
April 9, 2009 at 12:58 pm
Hi,
As per BOL (search "wildcard characters [SQL Server]"):
C. Using the ESCAPE clause
The following example uses the ESCAPE clause and the escape character to find the exact character string 10-15% in column c1 of the mytbl2 table.
USE tempdb;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2;
GO
USE tempdb;
GO
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off');
INSERT mytbl2 VALUES ('Discount is .10-.15 off');
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';
GO
April 9, 2009 at 1:01 pm
Hi Gail-
SELECT * FROM SysUser WHERE CHARINDEX('_' ,Name ) > 0 --> Works and gives me 4 records
select * from SysUser where name like '%/_%' --> No record.
April 9, 2009 at 1:05 pm
Did you forget the "escape" part in the actual query, or just in the forum?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 1:13 pm
Dev (4/9/2009)
SELECT * FROM SysUser WHERE CHARINDEX('_' ,Name ) > 0 --> Works and gives me 4 recordsselect * from SysUser where name like '%/_%' --> No record.
Create Table Wildcards (
SomeColumn varchar(10)
)
Insert into Wildcards values ('abc')
Insert into Wildcards values ('abc_')
Insert into Wildcards values ('abcdef')
Insert into Wildcards values ('abcde_f')
SELECT * FROM Wildcards WHERE CHARINDEX('_' ,SomeColumn ) > 0
-- 2 rows affected
SELECT * FROM Wildcards WHERE SomeColumn LIKE '%/_%' ESCAPE '/'
-- 2 rows affected
If you leave out the ESCAPE clause, it will match strings that literally have a '/' followed by at least one character.
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
April 14, 2009 at 11:49 pm
Hi GSquared,
Could you please tell me how to use square braces.
Thanks
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
April 15, 2009 at 2:55 pm
jchandramouli (4/14/2009)
Hi GSquared,Could you please tell me how to use square braces.
Thanks
Hi
LIKE '%[_]%'
What is the problem with the ESCAPE or CHARINDEX?
Greets
Flo
April 16, 2009 at 2:35 am
Personally, I prefer to use CharIndex when you need to find a simple string with no pattern matching. Otherwise, I prefer square brackets over the escape clause.
The reason you need to do this is that the underscore is one of the pattern operators.
Check out the link to my article on PatIndex in my signature for more information.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply