Find columns that contain all matches from a comma delimited list using TSQL

Question

I have a table "AddressSearch". In one column I have stored a comma separated list of strings.

eg: Table: AddressSearch

col1
-----------------
UK, east, london
UK, Cambridge, Museum
Maryland, Johns University

I also have another table named "Main" which has a column "full_address" that stores the full addresses in the format "xxx, east london, UK, E15 xxx".

I need a way to find all occurrences in the "Main" table where full_address contains all the strings that are comma separated in each row in the AddressSearch table.

eg: For the first row in AddressSearch, it should match all the rows in Main and filter out the rows that contain "UK" AND "east" AND "London".

I have already tried to split the strings into a table variable and do an inner join between Main and AddressSearch with PATINDEX. But this will only give me rows from Main that has either "UK" OR "east" OR "London".

Any suggestions?

Possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — underscore_d, Aug 16 '16 at 09:42

score 0 · Answer 1 · answered Aug 16 '16 at 09:32

When you replace something from a string and if the match is not found,entire string is returned.

select replace('abc','d','')--abc

Based on that and using split string function from here,wrote below logic..

create table #t
(
addr varchar(max)
)

insert into #t
select 'UK, east, london'

create table #main
(
addr1 varchar(max)
)

insert into #main
select 'xxx, east london, UK, E15 xxx'

;with cte
as
(select b.* from #t m
cross apply
(
select  * from [dbo].[SplitStrings_Numbers](m.addr,',')) b
)
select case when addr1<>rplc then 'Exists' else 'Not Exists' end as 'status',item
 from #main m
cross apply
(select replace(m.addr1,c.item,'') as rplc,c.item from cte c)b

Output:

   status   item
  Exists    UK
  Exists     east
  Exists     london

Thanks a lot @TheGameiswar. What if I want to get a distinct list of addresses from the Main table where all the 'item's exist? — user2463061, Aug 16 '16 at 10:16
this was based on first example and demo data i set up,you could actually get string from cte (see main) ,instead of restricting,you could get all — TheGameiswar, Aug 16 '16 at 10:21

score 0 · Accepted Answer · answered Aug 16 '16 at 10:55

Perhaps this may help

Declare @Main table (Full_Address varchar(150))
Insert into @Main values
('xxx, east london, UK, E15 xxx'),
('The Museum, Cambridge , UK, E25 xxx'),
('Mary Land, University , UK, E25 xxx')   

Declare @AddressSearch table (col varchar(150))
Insert into @AddressSearch values
('UK, east, london'),
('UK, Cambridge, Museum'),
('Maryland, Johns University')

;with cteSearch as (Select *,RowNr=Row_Number() over (Order by Col) from @AddressSearch
    ),cteParsed as (Select RowNr,B.Key_Value,Items=count(*) over (Partition By RowNr) From  cteSearch A Cross Apply (Select * From [dbo].[udf-Str-Parse](A.col,',')) B
    ),cteFinal  as (Select A.Full_Address,Col,Items,Hits  = count(*) over (Partition By Full_Address,B.RowNr) From @Main A Join cteParsed B on (charindex(B.Key_Value,A.Full_Address)>0) Join cteSearch C on (B.RowNr=C.RowNr)
    )
    Select Distinct * from cteFinal Where Hits=Items

Returns

Full_Address                           Col                      Items   Hits
The Museum, Cambridge , UK, E25 xxx    UK, Cambridge, Museum    3       3
xxx, east london, UK, E15 xxx          UK, east, london         3       3

Forgot the UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Thanks @John That's exactly what I was looking for. – user2463061 Aug 16 '16 at 14:44 — user2463061, Aug 16 '16 at 14:44

Find columns that contain all matches from a comma delimited list using TSQL

2 Answers2