CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
StealthRT
StealthRT

Reputation: 10542

INFORMATION_SCHEMA.COLUMNS removing column name from results

I have the following query:

USE db183
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'SCHEDULE_%' 
   OR COLUMN_NAME LIKE 'EMPINFO_%' 
   OR COLUMN_NAME LIKE 'AVAIL_%' 
   OR COLUMN_NAME LIKE 'TRAINING_%' 
ORDER BY 
    CASE
        WHEN COLUMN_NAME LIKE 'SCHEDULE_%' THEN 0  
        WHEN COLUMN_NAME LIKE 'EMPINFO_%' THEN 1 
        WHEN COLUMN_NAME LIKE 'AVAIL_%' THEN 2 
        WHEN COLUMN_NAME LIKE 'TRAINING_%' THEN 3
    END, ORDINAL_POSITION ASC

And I am wanting to not include the column name EMPINFO_empName.

However, when trying this:

USE db183
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'SCHEDULE_%' 
   OR COLUMN_NAME LIKE 'EMPINFO_%' 
   OR COLUMN_NAME LIKE 'AVAIL_%' 
   OR COLUMN_NAME LIKE 'TRAINING_%' 
   AND NOT COLUMN_NAME LIKE '%_empName' /*<-- NEW ADDED CODE HERE */
ORDER BY 
    CASE
        WHEN COLUMN_NAME LIKE 'SCHEDULE_%' THEN 0  
        WHEN COLUMN_NAME LIKE 'EMPINFO_%' THEN 1 
        WHEN COLUMN_NAME LIKE 'AVAIL_%' THEN 2 
        WHEN COLUMN_NAME LIKE 'TRAINING_%' THEN 3
    END, ORDINAL_POSITION ASC

Does not seem to take out that particular column name - It's still listed in the output just like the first query.

I'm guessing that I have some syntax incorrect but I do not get any errors.

Upvotes: 0

Views: 575

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

You need to escape the underscore in your LIKE statement:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'SCHEDULE[_]%' OR
      COLUMN_NAME LIKE 'AVAIL[_]%' OR
      COLUMN_NAME LIKE 'TRAINING[_]%' OR
      (COLUMN_NAME LIKE 'EMPINFO[_]%' AND COLUMN_NAME NOT LIKE '%[_]empName%')

Actually, if you really just want to exclude a single column then just use the full name:

WHERE COLUMN_NAME <> 'EMPINFO_empName'

Upvotes: 0

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

It is not about information_schema only.

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (COLUMN_NAME LIKE 'SCHEDULE_%' 
   OR COLUMN_NAME LIKE 'EMPINFO_%' 
   OR COLUMN_NAME LIKE 'AVAIL_%' 
   OR COLUMN_NAME LIKE 'TRAINING_%' ) --this is what you need
   AND NOT COLUMN_NAME LIKE '%_empName' /*<-- NEW ADDED CODE HERE */

Upvotes: 2

Related Questions