CODEWITHSUNDEEP
sqlsql-server
TomuRain
TomuRain

Reputation: 275

LIKE operator and % wildcard when string contains underscore

I have a table in SQL Server that stores codes. Depending on the nomenclature, some begin with 'DB_' and others with 'DBL_'. I need a way to filter the ones that start with 'DB_', since when I try to do it, it returns all the results.

CREATE TABLE CODES(Id integer PRIMARY KEY, Name Varchar(20));

INSERT INTO CODES VALUES(1,'DBL_85_RC001');
INSERT INTO CODES VALUES(2,'DBL_85_RC002');
INSERT INTO CODES VALUES(3,'DBL_85_RC003');
INSERT INTO CODES VALUES(4,'DB_20_SE_RC010');
INSERT INTO CODES VALUES(5,'DB_20_SE_RC011');

SELECT * FROM CODES where Name like 'DB_%';

The result that returns:

1|DBL_85_RC001
2|DBL_85_RC002
3|DBL_85_RC003
4|DB_20_SE_RC010
5|DB_20_SE_RC011

Expected result:

4|DB_20_SE_RC010
5|DB_20_SE_RC011

Upvotes: 0

Views: 1137

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The underscore is a wildcard in SQL Server. You can escape it:

where name like 'DB$_%' escape '$'

You could also use left():

where left(name, 3) = 'DB_'

However, this is not index- and optimizer friendly.

Upvotes: 2

Thom A
Thom A

Reputation: 95554

_ is a wildcard for a single character in a LIKE expression. Thus both 'DB_' and 'DBL' are LIKE 'DB_'. If you want a literal underscore you need to put it in brackets ([]):

SELECT *
FROM CODES
WHERE [Name] LIKE 'DB[_]%';

Upvotes: 4

Related Questions