CODEWITHSUNDEEP
sql-servert-sql
Rod
Rod

Reputation: 15423

sql like statement picking up unexpected results

I have a simple table like the following

id, target
-----------
1, test_1
2, test_2
3, test_3
4, testable

I have a simple query like so:

select * from my_table where target like 'test_%'

What I'm expecting are the first 3 records but I'm getting all 4 records

See SQLFiddle example here

Upvotes: 3

Views: 300

Answers (4)

Jim Horn
Jim Horn

Reputation: 889

The underscore character _ as you've used it is a wildcard for a single character, hence it returns 4 rows. Try using [_] instead of _.

To illustrate..

CREATE TABLE #tmp (val varchar(10)) 

INSERT INTO #tmp (val)
VALUES ('test_1'), ('test_2'), ('test_3'), ('testing')

-- This returns all four
SELECT * FROM #tmp WHERE val LIKE 'test_%'

-- This returns the three test_ rows
SELECT * FROM #tmp WHERE val LIKE 'test[_]%'

Upvotes: 4

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

_ is also a wildcard. You can escape it like:

... like 'test\_%' escape '\'

Upvotes: 6

pmbAustin
pmbAustin

Reputation: 3970

The underscore is a wildcard character that says "match any character single character", just like the % is a wildcard that says "match any 0 or more characters". If you're familiar with Regular Expressions, the underscore character is equivalent to the dot there. You'll need to properly escape the underscore to match that character literally.

Upvotes: 3

Steve Greene
Steve Greene

Reputation: 12304

Underscore is a pattern matching character. Try this: 

select * from my_table where target like 'test[_]%'

Upvotes: 6

Related Questions