StealthRT StealthRT - 6 months ago 16
SQL Question

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.

Answer

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 */