CODEWITHSUNDEEP
sqlsql-server
alwaysVBNET
alwaysVBNET

Reputation: 3310

Wildcard query % fetches wrong data

I am trying to select the tables from my database where they start with srt_factor_ (which in this case are only the first two).

My tables are:

srt_factor_20121119
srt_factor_20130430
srt_factorxyzk_20130813

My query erroneously returns instead of the first two tables only, the last one as well (srt_factorxyzk_20130813)

Select (TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 
AND TABLE_NAME LIKE 'srt_factor_%';

Any ideas how to fix it?

Upvotes: 1

Views: 62

Answers (3)

jpw
jpw

Reputation: 44871

Try escaping the underscore in brackets.

LIKE 'srt_factor[_]%';

Underscore is used to match any single character.

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 156938

The problem is in the underscores which have a special meaning in SQL (it's like the * wildcard, but for exact one character). You have to escape them like this:

TABLE_NAME LIKE 'srt[_]factor[_]%'

See MSDN on the use of wildcard characters as literals.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269553

'_' is a wildcard in SQL Server, representing one character. Try this instead:

Select (TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND 
      TABLE_NAME LIKE 'srt[_]factor[_]%';

Upvotes: 2

Related Questions