Low priority: Underscore not recognized in LIKE ?

[LNBruno]

We have a bunch of tables with an 'S_' prefix and I wanted to get the identity columns for them so I ran this:

SELECT o.name AS [Table Name]
   , c.name AS [Column Name] 
FROM sysobjects o 
JOIN syscolumns c 
    ON o.id = c.id
WHERE o.type = 'U' 
    AND COLUMNPROPERTY(o.id, c.name, 'IsIdentity') = 1
    AND o.name LIKE 'S_%'
ORDER BY 1

It worked okay, but returned other rows with tables beginning with 'S' (e.g., SpecialAssets, Statuses, SubAccounts).

Any idea why 'S_%' didn't return just the rows with table names that started with 'S_' ?


< M!ke >

 

[SQLDenis]

you have to escape it like this

SELECT o.name AS [Table Name]
   , c.name AS [Column Name] 
FROM sysobjects o 
JOIN syscolumns c 
    ON o.id = c.id
WHERE o.type = 'U' 
    AND COLUMNPROPERTY(o.id, c.name, 'IsIdentity') = 1
    AND o.name LIKE 'S[_]%'
ORDER BY 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Try...

AND o.name LIKE 'S[!][[/!]_[!]][/!]%'

The underscore is a special character for the like operator. By surrounding it in square brackets, you can actually search for it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Curse you Denis! [small]Just kidding.[/small]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

i know

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[LNBruno]

DOH! Shoulda knowed!

It's okay, George; I gave you a star, too. And yes, Denis, I gave you yours first!

Kids! We just never grow up, do we? ;-)

Thanks, guys!

< M!ke >