0

I need to find cases where individuals have entered an invalid response in a questionnaire. The list of valid responses is saved as a semicolon-separated string in a table, like this: "starfish;bass;cod;dolphin;shark;"

If I run the query against the text value, it will tell me that "starfish" is not in the list, because I haven't separated the values: 

SELECT 'starfish' as value, 'starfish;bass;cod;dolphin;shark;' as 'is not in this list' 
WHERE 'starfish' NOT IN ('starfish;bass;cod;dolphin;shark;')

I could go through a fairly manual process where I convert the semicolon list into a quote and comma list. When I do that, it works: 

SELECT 'starfish' as value, 'starfish'',''bass'',''cod'',''dolphin'',''shark' as 'is not in this list' 
WHERE 'starfish' NOT IN  ('starfish','bass','cod','dolphin','shark')

Now, here's where my problem lies: I'd like to save some steps by having my query convert the semicolon string into an apostrophe/comma separated list. However, when I do that, SQL acts as if my value is not in the list. 

SELECT 'starfish' as value, (''''+REPLACE(LEFT(RTRIM('starfish;bass;cod;dolphin;shark;'),LEN(RTRIM('starfish;bass;cod;dolphin;shark;'))-1),';',''',''')+'''') as 'is not in this list'
WHERE 'starfish' not in (''''+REPLACE(LEFT(RTRIM('starfish;bass;cod;dolphin;shark;'),LEN(RTRIM('starfish;bass;cod;dolphin;shark;'))-1),';',''',''')+'''')

It appears that SQL doesn't recognize my query-generated list of valid values as a list. Any way to fix?

Igor
  • 60,821
  • 10
  • 100
  • 175
Matt D
  • 1
  • 1
  • I think your trouble is the quotes themselves. 'starfish' should be `starfish`, for instance. (edit: the single quotes disappear in a comment. The character should be the one in the top-left of the keyboard.) – durbnpoisn Nov 28 '16 at 21:56
  • 1
    You are making it hard for yourself by storing the values as a string. Separate the answers in to rows and your search becomes a trivial `SELECT ... WHERE ...` or `EXISTS...` statement. There are loads of answers in SO for splitting a string in to rows, e.g. http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows – Tony Nov 28 '16 at 22:14
  • Which DBMS are you using? –  Nov 28 '16 at 22:37

1 Answers1

0

You could try to use LIKE operator and wildcards.

select * from 
(
SELECT 'starfish' as value, 'starfish;bass;cod;dolphin;shark;' as notlist from dual
)
WHERE ';'|| notlist not like '%;'||value||';%';
arturro
  • 1,598
  • 1
  • 10
  • 13