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

First you need to convert your lists (in recrods) to form such that it starts and ends with comma and contains no spaces. Then you can use charindex combined with or operator to check if particular values or included in records:

declare @TransferData  table (transferTypes varchar(20));
insert into @TransferData values
('TTH, TT'),
('TRANSIT, TTH'),
('ST, TRANSIT'),
('TRANSIT, TTH'),
('ST, TT');

select * from (
    select ',' + replace(transferTypes, ' ', '') + ',' transferTypes from @TransferData
) a
where charindex(',TT,', transferTypes) > 0
or charindex(',ST,', transferTypes) > 0