CODEWITHSUNDEEP
sql-servert-sqlsql-in
Santosh Jadi
Santosh Jadi

Reputation: 1525

SQL IN condtion

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

Upvotes: 12

Views: 309

Answers (4)

John
John

Reputation: 3996

How about something like this:

select 
  transferTypes 
from 
  TransferData 
where 
  transferTypes like '%TT%'
  or transferTypes like '%ST%'

Upvotes: 2

Matt
Matt

Reputation: 2869

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:

Three string rows returned

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.

Upvotes: 13

betelgeuce
betelgeuce

Reputation: 837

Here is an illustrative example on how to achieve this. Use variables obvious in the selects. You never specified how many items would be in your delimited list, so I make the assumption of 2 based on the sample data.

If it's varied then perhaps some form of Dynamic SQL might be the order of the day. 

create table #transferdata
( 
 transferTypes  varchar(100) 
) 

insert into #transferdata select 'TTH, TT'
insert into #transferdata select 'TRANSIT, TTH'
insert into #transferdata select 'ST, TRANSIT'
insert into #transferdata select 'TRANSIT, TTH'
insert into #transferdata select  'ST, TT'


;WITH tmp AS
(
    SELECT

        LEFT(transferTypes , CHARINDEX(',', transferTypes  + ',') - 1) as col1 ,
        ltrim(STUFF(transferTypes , 1, CHARINDEX(',', transferTypes  + ','), '')) as col2
    FROM #transferdata

)
SELECT * into #tmp 
FROM tmp;

select * from #tmp as t where (t.col1 = 'tt' or t.col2 = 'tt') or (t.col1 = 'st' or t.col2 = 'st')
select * from #tmp as t where (t.col1 = 'tth' or t.col2 = 'tth') or (t.col1 = 'transit' or t.col2 = 'transit')
select * from #tmp as t where (t.col1 = 'tt' or t.col2 = 'tt') or (t.col1 = 'tt' or t.col2 = 'tt')


drop table #tmp
drop table #transferdata

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

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

Upvotes: 3

Related Questions