Comparing string data with table data
I have a table with string values that must be individually mapped 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 retrieve records from table A.Col1 to match on TableA.col2 = TableB.col1
Below is the sql code:
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
Your best bet would be to normalize your tables, so TableA will have one row per Col2 data. You could join tables very easily and quickly. If you don't, you can try this:
Before using my function, you need to set up the helper table, you only need to do this once for each 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 doesn't work 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 request 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
try, this might 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 the following:
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 matches:
SELECT
a.*
FROM TableA2 a
INNER JOIN TableB b ON a.Col2=b.Col2
to complete the change if you want you can delete table TableA and then rename TableA2 to TableA
source to share