SQL IN condtion

Question

select transferTypes from TransferData 

transferTypes 
--------------
TTH, TT
TRANSIT, TTH
ST, TRANSIT
TRANSIT, TTH
ST, TT

Is there is any way or inbuilt function to achieve below results?

Tried with below IN condition but unable to get required results.

Expecting Result:

select transferTypes from TransferData where transferTypes in ('TT, ST')

transferTypes 
-------------
TTH, TT
ST, TRANSIT
ST, TT

select transferTypes from TransferData where transferTypes in ('TTH, TRANSIT')

transferTypes 
-------------
TTH, TT
TRANSIT, TTH
ST, TRANSIT
TRANSIT, TTH

select transferTypes from TransferData where transferTypes in ('TT')

transferTypes 
-------------
TTH, TT
ST, TT

Answer 1

Easiest way is using string_split which was introduced in SQL Server 2016 and later.

SELECT 
    DISTINCT a.transferTypes
FROM 
    TransferData a
CROSS APPLY 
    string_split([transferTypes], ',') b
WHERE 
    TRIM(b.[value]) IN ('TT', 'ST')

The above splits out all values in transferTypes and allows you to search by individual values. If you're using a version lower than SQL Server 2016, you can always create a function to do the exact same (E.g. T-SQL split string )

================================================

A little explanation of what string_split does:

string_split is a table valued function which in short means that the function will output a table. Given a string input, string_split will output multiple rows of substrings based on a delimiter that you specify.

Take the following for example:

SELECT 
    *
FROM 
    string_split('String1;String2;String3', ';')

The above code will return three rows as shown below:

This is very powerful for the original question as it allows us to filter directly to single values without needing to use CHARINDEX or LIKE.