SQL Server PATINDEX to match exact string

Question

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.

Answer 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
    );