0

I'm working on an SQL statement in SQL Server Managment Studio and I've come across a problem. I want to take a table which is full of strings that are lists delineated by a ','. Then use the NOT IN to compare them to a list of values. It would look something like this:

commaStr = '6F, 0F, 0W'

Where (string is converted here) NOT IN ('0F', '0R', '0W')

I'm unsure of how to go about this so I'm hoping for just some general direction. I've seen a lot of people create a function to handle this solution but I was curious if you could set something up compare the two in the where clause. My starting thought were definitely some sort of loop, but I'm not sure if it's possible to set one up in the where clause.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Lazed
  • 9
  • 2
  • 1
    Those are strings, not lists. `IN` has no meaning because there's only one string value. Storing data this way is a design bug in the first place, violating the most basic database design rule. You can use `STRING_SPLIT` to split the string into values but you won't be able to take advantage of indexes so the query will be slow. The real solution is to fix the design bug - either use one column per flag or use a many-to-many table – Panagiotis Kanavos Jul 29 '21 at 13:34
  • @PanagiotisKanavos unfortunately this is the way my system admin has chosen to do it. I was curious as to if it was even a possibility. – Lazed Jul 29 '21 at 13:41
  • It doesn't matter what someone decided to do. It doesn't matter if someone proclaims that the Earth is flat. It's not. That's a serious design bug that will force full table scans (and table-level Shared locks) no matter what you try. With `STRING_SPLIT` at least you can avoid bad matches. The query will still lock the entire table. Never mind the garbage stored in there - without real values, you can't use constraints or foreign keys to ensure the data is clean – Panagiotis Kanavos Jul 29 '21 at 13:55
  • 1
    The *only* way to get this to work is to use a database with real *indexed* arrays. There are very few of those, precisely because it's the *table's* job to store such things. Never mind constraints, ensuring the stored values make sense etc. Until the design bug is fixed you can try `FROM someTable CROSS APPLY STRING_SPLIT(csvField) as x WHERE x.value NOT IN ('0F', '0R', '0W') ` – Panagiotis Kanavos Jul 29 '21 at 13:59
  • Table valued parameter or a table variable would probably be wise. But yeah, your current design is denormalized, you should fix it. What kind of matching do you actually want: Must not be an exact match of the two tables? Or no matches from the parameter list can match the table column? Some other logic? – Charlieface Jul 29 '21 at 15:40
  • It is unclear what you want to do. Do you want to know when all the values are not in the list? When any value is not in the list? Something else? – Gordon Linoff Jul 29 '21 at 16:24
  • @GordonLinoff I want to know when any value is not in the list – Lazed Jul 29 '21 at 21:11

0 Answers0