0

Can somebody please help me to understand why the IN() function does not work in any of the below attempts?

DECLARE @Company VARCHAR(50) = ('''ABC''' + ', ' + '''DEF''' + ', ' + '''VEG''' + ', ' + '''HIJ''') --+ WNY, VEG''')
PRINT @Company
PRINT IIF('VEG' IN (@Company), 'TRUE', 'FALSE');


DECLARE @Company2 VARCHAR(50) = ('''ABC, DEF, VEG, HIJ''')
PRINT @Company2
PRINT IIF('VEG' IN (@Company2), 'TRUE', 'FAL

Tried running the command as presented and both IIF()s return false.

Thom A
  • 88,727
  • 11
  • 45
  • 75
JackB
  • 1
  • 1
  • 5
    You can't just take a **scalar** variable containing a delimited varchar and expect it to work like a list of literals. A **`varchar`** with the value `'abc','def'` is *not* the same as 2 separate `varchar`s with the values `abc` and `def`. Use a table type variable/parameter or split your delimited list into a dataset. `IN ('''abc'',''def''')` is equivalent to `= '''abc'',''def'''`, and hence why it doesn't work as you expect – Thom A Nov 10 '22 at 16:53
  • see if one of these will help you https://stackoverflow.com/questions/41619245/find-in-set-equivalent-in-sql-server – nbk Nov 10 '22 at 16:55
  • 1
    Side note: `IN` isn't a function, it's an [operator](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operators-transact-sql?view=sql-server-ver16) (like `=`, `>`, `AND` and `BETWEEN`). – Thom A Nov 10 '22 at 16:58
  • I've assumed you are using SQL Server here, based on the [[tag:ssms]] tag, however, if you are using a different product that can be connected to via SSMS, such as Azure SQL Edge, or Azure SQL Database, please [edit] your question to correct the tag(s). – Thom A Nov 10 '22 at 17:02
  • Just adding commas and quotes to a string doesn't magically turn it in to a set of multiple values, it's still just a single string value. If you add your values to a table than you can use `'VEG' IN (SELECT company FROM your_table)`. Or if your dbms has support, you can use functions that convert a string in to multiple values `'VEG' IN (SELECT value FROM STRING_SPLIT(@company, ',') AS company_list)` – MatBailie Nov 10 '22 at 17:08
  • Look at CHARINDEX, testing for a charindex > 0 could be the test you want – Robert Sheahan Nov 10 '22 at 17:19

0 Answers0