SELECT statement using NOT IN against a text string

Question

I want to write a SELECT statement that returns value but excludes any that appear in a text list

So I have a text list COL1, COl2, COL4, COL5 (called myString)

And I want to say...

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'INTERFACE' AND COLUMN_NAME NOT IN(myString);

How would I do that?

I am trying to solve this problem in a stored procedure and though that just getting back to basics would help me understand. My SP code looks like this...

INSERT INTO @excludeFieldTable VALUES ('CASENO'),('INTLINENO'),('INTMEMBNO'),('INTSTATUS'),('RECTYPE'),('EFFDTE'),('INTERR'),('INTERR2'),
('INTWARN'),('AUTHORISED'),('EXCLUDE'),('UPDATED'),('INTMEMBNO2'),('INTSTATUS2'),('ALTKEY');

    INSERT INTO @intColumnList SELECT DISTINCT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='INTERFACE' AND COLUMN_NAME NOT IN (SELECT DISTINCT(excludeFieldName) FROM @excludeFieldTable);

but the NOT IN against the @excludeFieldTable is not evaluating for some reason (so intColumnList still has the values 'CASENO', 'INTLINENO' etc)

Check out the following solution http://www.spyriadis.net/2010/12/convert-a-comma-separated-nvarchar-to-a-list-that-can-be-used-in-any-t-sql/ — TGH, Oct 04 '13 at 14:05
Sorry for answer with way too many mistakes, @freefaller and Mike. Was going to try to fix, bu t thought better to remove instead. Did too fast. — asantaballa, Oct 04 '13 at 14:46

score 1 · Answer 1 · answered Oct 04 '13 at 14:02

1

You can't put a single comma seperated string into the IN clause. Either use seperate values of put it together dynamically using EXEC

exec('SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME=''INTERFACE'' 
      AND COLUMN_NAME NOT IN (' + @myString + ')')

answered Oct 04 '13 at 14:02

juergen d

201,996
37
293
362

You need double "single quotes" on the `INTERFACE` – freefaller Oct 04 '13 at 14:05

jenson-button-event · Answer 2 · 2013-10-04T14:11:20.047

1

Exec('
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME=''INTERFACE'' 
AND COLUMN_NAME NOT IN(' + @myString + ')')

Another popular option is to use a split function and then join to it: See here: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/

edited Oct 04 '13 at 14:11

answered Oct 04 '13 at 14:02

jenson-button-event

18,101
11
89
155

You need double "single quotes" on the `INTERFACE` – freefaller Oct 04 '13 at 14:06

Fabien TheSolution · Answer 3 · 2013-10-04T15:22:45.753

There is no reason why your approach is not working. Check again at your code :

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE INTERFACE
    ([ID] int, 
     [COL1] varchar(2), 
     [COL2] varchar(2), 
     [COL3] varchar(2), 
     [COL4] varchar(2), 
     [CASENO] int,
     [INTLINENO] int,
     [INTMEMBNO] int,
     [INTSTATUS] int,
     [RECTYPE] varchar(2),
     [EFFDTE] date,
     [INTERR] int,
     [INTERR2] int,
     [INTWARN] int,
     [AUTHORISED] varchar(12),
     [EXCLUDE] varchar(10),
     [UPDATED] varchar(6),
     [INTMEMBNO2] int,
     [INTSTATUS2] int,
     [ALTKEY] varchar(12),
     [COL5] varchar(2))
;

Query 1:

DECLARE @excludeFieldTable TABLE
(
  excludeFieldName varchar(100)
)
INSERT INTO @excludeFieldTable VALUES 
('CASENO'),('INTLINENO'),('INTMEMBNO'),('INTSTATUS'),
('RECTYPE'),('EFFDTE'),('INTERR'),('INTERR2'),
('INTWARN'),('AUTHORISED'),('EXCLUDE'),('UPDATED'),
('INTMEMBNO2'),('INTSTATUS2'),('ALTKEY')


DECLARE @intColumnList TABLE
(
  columnList varchar(100)
)
INSERT INTO @intColumnList 
SELECT DISTINCT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='INTERFACE' AND 
COLUMN_NAME NOT IN (SELECT DISTINCT(excludeFieldName) FROM @excludeFieldTable)

SELECT *
FROM @intColumnList

Results:

| COLUMNLIST |
|------------|
|       COL1 |
|       COL2 |
|       COL3 |
|       COL4 |
|       COL5 |
|         ID |

EDITED :

With a string, it can look like this :

Query 2:

DECLARE @excludeFieldTable VARCHAR(1000)

SET @excludeFieldTable = 'CASENO, INTLINENO, INTMEMBNO, INTSTATUS, RECTYPE, EFFDTE, INTERR, INTERR2, INTWARN, AUTHORISED, EXCLUDE, UPDATED, INTMEMBNO2, INTSTATUS2, ALTKEY'

DECLARE @intColumnList TABLE
(
  columnList varchar(100)
)
INSERT INTO @intColumnList 
SELECT DISTINCT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='INTERFACE' AND 
', '+@excludeFieldTable+', ' NOT LIKE '%, '+COLUMN_NAME+', %'

SELECT *
FROM @intColumnList

Results:

| COLUMNLIST |
|------------|
|       COL1 |
|       COL2 |
|       COL3 |
|       COL4 |
|       COL5 |
|         ID |

RE-EDITED :

SP working fine also :

Query 3:

CREATE PROCEDURE ExcludeField1 AS
DECLARE @excludeFieldTable VARCHAR(1000)

SET @excludeFieldTable = 'CASENO, INTLINENO, INTMEMBNO, INTSTATUS, RECTYPE, EFFDTE, INTERR, INTERR2, INTWARN, AUTHORISED, EXCLUDE, UPDATED, INTMEMBNO2, INTSTATUS2, ALTKEY'

DECLARE @intColumnList TABLE
(
  columnList varchar(100)
)
INSERT INTO @intColumnList 
SELECT DISTINCT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='INTERFACE' AND 
', '+@excludeFieldTable+', ' NOT LIKE '%, '+COLUMN_NAME+', %'

SELECT * FROM @intColumnList

Results:

Query 4:

CREATE PROCEDURE ExcludeField2 AS
DECLARE @excludeFieldTable TABLE
(
  excludeFieldName varchar(100)
)
INSERT INTO @excludeFieldTable VALUES 
('CASENO'),('INTLINENO'),('INTMEMBNO'),('INTSTATUS'),
('RECTYPE'),('EFFDTE'),('INTERR'),('INTERR2'),
('INTWARN'),('AUTHORISED'),('EXCLUDE'),('UPDATED'),
('INTMEMBNO2'),('INTSTATUS2'),('ALTKEY')

DECLARE @intColumnList TABLE
(
  columnList varchar(100)
)
INSERT INTO @intColumnList 
SELECT DISTINCT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='INTERFACE' AND 
COLUMN_NAME NOT IN (SELECT DISTINCT(excludeFieldName) FROM @excludeFieldTable)

SELECT * FROM @intColumnList

Results:

Query 5:

EXEC ExcludeField1

Results:

| COLUMNLIST |
|------------|
|       COL1 |
|       COL2 |
|       COL3 |
|       COL4 |
|       COL5 |
|         ID |

Query 6:

EXEC ExcludeField2

Results:

| COLUMNLIST |
|------------|
|       COL1 |
|       COL2 |
|       COL3 |
|       COL4 |
|       COL5 |
|         ID |

If you want to define the exclude string before, it will look like this :

Query 7:

CREATE PROCEDURE ExcludeField1 @excludeFieldString varchar(1000) AS

DECLARE @intColumnList TABLE
(
  columnList varchar(100)
)
INSERT INTO @intColumnList 
SELECT DISTINCT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='INTERFACE' AND 
', '+@excludeFieldString+', ' NOT LIKE '%, '+COLUMN_NAME+', %'

SELECT * FROM @intColumnList

Results:

Query 8:

DECLARE @excludeFieldTable VARCHAR(1000)

SET @excludeFieldTable = 'CASENO, INTLINENO, INTMEMBNO, INTSTATUS, RECTYPE, EFFDTE, INTERR, INTERR2, INTWARN, AUTHORISED, EXCLUDE, UPDATED, INTMEMBNO2, INTSTATUS2, ALTKEY'

EXEC ExcludeField1 @excludeFieldString = @excludeFieldTable

Results:

| COLUMNLIST |
|------------|
|       COL1 |
|       COL2 |
|       COL3 |
|       COL4 |
|       COL5 |
|         ID |

Thanks @Fabien - could it be the data types - I see your 2 tables both have varchar(5) - my excludeFieldTable has varchar(100) - would it still work if this was a different setting to the COLUMN_NAME column in the INFORMATION_SCHEMA.COLUMNS table? — Mike, Oct 04 '13 at 14:35
I ran your code on the query pane and it worked. I ran it again from my SP and it did not. Is there anything I need to do to "commit" the insert to the excludeFieldTable before I then reference it in the NOT IN query? — Mike, Oct 04 '13 at 14:40
I edited my answer to consider your actual fields. There is nothing special to do. — Fabien TheSolution, Oct 04 '13 at 14:48

score 0 · Answer 4 · answered Oct 04 '13 at 15:04

0

Apologies to all (and a huge thanks for your time) - there was a small typo in my SP code

answered Oct 04 '13 at 15:04

Mike

2,391
6
33
72

I was almost sure that it was the code :) – Fabien TheSolution Oct 04 '13 at 15:29
@FabienTheSolution - you were absolutely correct. Thanks again for taking the time to help me. Really appreciate it – Mike Oct 04 '13 at 15:46

SELECT statement using NOT IN against a text string

4 Answers4