SQL Select Statement where between two lists

user_2506314 0
Viewed 533

I am trying to perform a select statement:

SELECT group_id
FROM ACCOUNT_GROUPS 
WHERE account_id = '1' 
  AND admin_user = '0' 
  AND active = '1' 
  AND uname IN (<cfqueryparam value="#send_to_var#" cfsqltype="cf_sql_varchar" list="yes" />) 
  AND uname != ''

If uname is a single var like abc1 it works fine. But now we need to search more....

So uname now has a potential list like "abc1,abc2,abc3,abc4"

AND now #send_to_var# could also contain more than one var like "abc3,efd6,asc9"

So I've tried CONTAINS and other SQL techniques but keep running into issues.

Can anyone provide any suggestions?

coldfusion sql-server-2012 sql-server sql string
2 Answers

If you are using SQL Server 2016 there is a STRING_SPLIT function which will break up that uname column into a table which you can search with your list cfqueryparm. Your query will look something like:

SELECT group_id
FROM ACCOUNT_GROUPS 
WHERE account_id = '1' 
  AND admin_user = '0' 
  AND active = '1' 
  AND EXISTS ( SELECT * FROM STRING_SPLIT (uname,',') 
    WHERE VALUE IN (<cfqueryparam value="#send_to_var#" cfsqltype="cf_sql_varchar" list="yes" />) 
  AND uname != ''

The performance could be awful - this is considered bad data design for a reason. If your SQL Server doesn't support STRING_SPLIT there are other solutions, ranging from bad to worse. Edit your question to post your SQL Server version.

user_141267100

As others have already explained why, this looks like nasty DB design. But if you are really forced to persist with it, and you do not have STRING_SPLIT support, then a horrible hack would be:

SELECT group_id
FROM ACCOUNT_GROUPS 
WHERE account_id = '1' 
  AND admin_user = '0' 
  AND active = '1' 
  AND (1=0 <cfloop list="#send_to_var#" index="thisItem"> OR ','+uname+',' LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%,#thisItem#,%" /> </cfloop>)
  AND uname != ''

This assumes from the example data in your question, that your list delimiter is , without whitespace, and that there are no unescaped , characters allowed in the values stored in that list.

If the table is large then the leading wildcard will make this slow. But that's the least of your worries compared to the data design. Preferably plead with whoever is in charge for the time to overhaul that!

user_45757620
Related