Compare string data to table data

Question

I have a table with string values that need to matched individually to a column. For example

Table A

Col1    col2
 1234   0029QS, 0029QT, 0029QU, 0029QV
 2222   0006AG_0042
 2343   0032BP, 0032BQ, 0032BR
 1232   5000_0708, 5000_0709, 5000_0710

Table B

0029QS
0032BQ
0006AG_0042
5000_0709

I need to be able to match and pull records from Table A.Col1 for a match on TableA.col2 = TableB.col1

The sql is given below:

Create Table TableA(Col1 int , Col2 varchar(max) )
INSERT INTO TableA
Select   1234, '029QS, 0029QT, 0029QU, 0029QV'
 INSERT INTO TableA
Select   2222, '0006AG_0042'
 INSERT INTO TableA
Select   2343,   '0032BP, 0032BQ, 0032BR'
 INSERT INTO TableA
Select   1232,  '5000_0708, 5000_0709, 5000_0710'

Go

Create table TableB(Col2 Varhcar(50) )
INSERT INTO TableB
Select '0029QS'
INSERT INTO TableB
Select '0032BQ'
INSERT INTO TableB
Select '0006AG_0042'
INSERT INTO TableB
Select '5000_0709'
INSERT INTO TableB
Select '5000_0710'
GO

Answer 1

Your best bet would be to normalize your tables, so TableA would have one row per Col2 data. You could then join the tables very easily and quickly. Wihtout doing that, you could try the following:

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function in this query to solve your problem...

select
    a.Col1,b.Col2,a.Col2,b.Col2
    FROM TableA  a
    INNER JOIN TableB b On b.Col2 IN (SELECT ListValue FROM dbo.FN_ListToTable(',',a.Col2))

OUTPUT

Col1        Col2          Col2                           
----------- ------------- -------------------------------
2222        0006AG_0042   0006AG_0042                    
2343        0032BQ        0032BP, 0032BQ, 0032BR         
1232        5000_0709     5000_0708, 5000_0709, 5000_0710
1232        5000_0710     5000_0708, 5000_0709, 5000_0710

EDIT
give this a try, it may be faster:

select
    a.Col1,b.Col2,a.Col2
    FROM TableA                                     a
        CROSS APPLY dbo.FN_ListToTable(',',a.Col2)  a2 
        INNER JOIN TableB                           b On a2.ListValue=b.Col2

If you want to normalize your tables, do this:

Create Table TableA2(Col1 int , Col2 varchar(50) )

don't forget to create an index on Col2

INSERT INTO TableA2
SELECT
    a.Col1, b.ListValue
    FROM TableA  a
        CROSS APPLY dbo.FN_ListToTable(',',a.Col2) b

now to find the matches:

SELECT
    a.*
    FROM TableA2           a
        INNER JOIN TableB  b ON a.Col2=b.Col2

to complete the change, if you want, you can drop table TableA and then rename TableA2 to TableA

Answer 2

Can you normalize the table with col2 values so it's more like:

Col1    col2
1234   0029QS
1234   0029QT
1234   0029QU
1234   0029QV
2222   0006AG_0042
2343   0032BP
2343   0032BQ
2343   0032BR

Then you can do an inner join on col2 and table b