Trying to Sql join on a column with multiple text

Question

I have a SQL Server tables with states in them and another table with states in them but multiple states in a cell:

StateTable1

           Col 1             Col 2            
Row1      FullName          WA, OH, FL

GeographyTable2

            Col 1            Col 2            
Row1      Washington           WA
Row2        Ohio               OH
Row3       Florida             FL

FinalResultingTable3

           Col 1              Col 2            
 Row1     FullName             WA
 Row2     FullName             OH
 Row3     FullName             FL

What I want to do is join the Geographycol2 table to Statecol2 table and return the final table where I basically return multiple rows for each state.

Maybe the join isn't the place to do this is there a way to transpose these csv into rows with the same fullname?

Thanks for the help

Attempted query (where Contact is State table):

Select Top 1000
    Ct.fullname, Ct.ActiveLicenses, Geo.state
From 
    datawarehouse.wby_vw_dim.contact Ct
join 
    datawarehouse.wby_vw_dim.geography Geo on Geo.state like '%' + Ct.activelicenses + '%'

and this just returns one fullname infinite times

proper fix would be to normalize those csv fields into their own table. — Marc B, Sep 17 '15 at 15:22
I think this answer will help you: [http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows][1] [1]: http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — AZV, Sep 17 '15 at 15:24
Hey Marcb unfortunately it sits in a data warehouse that I do not have the ability to change the ETL process only to query from — Srboarder91, Sep 17 '15 at 15:25
`Row1` is some kind of ID or is just for reference? because i dont see if you join both table or not — Juan Carlos Oropeza, Sep 17 '15 at 15:41
Shouldnt the result show the full state name like `Washington`? — Juan Carlos Oropeza, Sep 17 '15 at 15:50

score 0 · Answer 1 · answered Sep 17 '15 at 15:41

0

change the join to

on Ct.activelicenses like '%' + Geo.state + '%'

instead of

on Geo.state like '%' + Ct.activelicenses + '%'

answered Sep 17 '15 at 15:41

Hedinn

864
4
7

score 0 · Answer 2 · answered Sep 17 '15 at 15:48

Playing with your example...

select 
  GeographyTable2.column1,
  StateTable1.column1,
  GeographyTable2.column2
from
  GeographyTable2 left join StateTable1 on 
    StateTable1.column2 like '%'+GeographyTable2.column2+'%';

You can find a SQL Fiddle demo here.

score 0 · Answer 3 · answered Sep 17 '15 at 15:53

Your best bet it is to put the data into a related table. Since you say you can't fix the schema, then you need to create a temp table and split the data out. Then join to the temp table as you would if you have this data correctly designed in the first place. Here is an example of how you would create the data for them temp table:

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END


select * from dbo.fnSplitString('q,d,b', ',') split
create table #test (Id int, mytest varchar (10))

insert into #test
values (1, 'a,b')
, (2, 'c,d,e')
, (3, 'f,g')
, (4, 'h')

create table #Splitdata (ID int, splitdata varchar (10))

insert into #Splitdata (id, Splitdata)
select t.id, split.splitdata from #test t
cross apply dbo.fnSplitString(mytest, ',') split

Note that I borrowed the split function from here: http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

Once you have the data into #splitdata table, you then join to it like normal using it a s a junction table between you two original table.s

score 0 · Answer 4 · edited May 23 '17 at 10:26

I use a recursive cte to split the string and remove extra spaces. Then join to the other table.

Sql Fiddle Demo

;with tmp([Col 1], DataItem, [Col 2]) as (  
    select 
        [Col 1], 
        LEFT([Col 2], CHARINDEX( ',', [Col 2]+',')-1), 
        ltrim(rtrim(STUFF([Col 2], 1, CHARINDEX(',',[Col 2]+','), '')))
    from StateTable1
    union all
    select 
        [Col 1], 
        LEFT([Col 2], CHARINDEX(',',[Col 2]+',')-1),
        ltrim(rtrim(STUFF([Col 2], 1, CHARINDEX(',',[Col 2]+','), '')))
    from tmp
    where [Col 2] > ''
)
SELECT t.[Col 1], DataItem, g.[Col 1]
FROM tmp t 
inner join GeographyTable2 g 
on t.DataItem = g.[Col 2]

OUTPUT

|    Col 1 | DataItem |      Col 1 |
|----------|----------|------------|
| FullName |       WA | Washington |
| FullName |       OH |       Ohio |
| FullName |       FL |    Florida |

I use this example

In your example you return `FullName` but if that is the case you don't need the second table `GeographyTable2` — Juan Carlos Oropeza, Sep 17 '15 at 15:59

Trying to Sql join on a column with multiple text

4 Answers4

OUTPUT