0
SELECT * FROM SYS.COLUMNS WHERE
NAME NOT IN (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'XXXX'
AND PATINDEX('%' + COLUMN_NAME + '%', 'ABC;XYZ') <> 0);

In Table XXXX there are columns like XY,AB,ABC,XYZ. Now I only want to exclude XYZ and ABC columns and keep the AB and XY columns using PATINDEX. I'm not able to do it because only the first column is getting excluded. And if I remove the beginning % from PATINDEX then both XYZ a XY are being removed. How can I solve this.

Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125

1 Answers1

1

You want CHARINDEX, not PATINDEX.

   SELECT * FROM SYS.COLUMNS 
    WHERE NAME NOT IN (
      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = 'XXXX'
      AND CHARINDEX(COLUMN_NAME + ';', 'ABC;XYZ;') > 0
    );
kgu87
  • 2,050
  • 14
  • 12
  • What I want to know is that should I put column names using a `;` separator or is there a better way? Also your query selects both `XY` and `XYZ` which is not the desired behavior. – Soham Dasgupta Jun 21 '13 at 04:53
  • 1
    See updated answer to deal with XY and XYZ selection - added ';' terminator to search strings. It does not matter what the separator is, since you're searching for occurrences of strings. – kgu87 Jun 21 '13 at 11:25