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