October 9, 2013 at 2:44 pm
Hello Everyone
I am working on an issue that involves string splitting. It really bothers me that people will Not design a database correctly, and ends up storing a delimited list in a single column.
I do have the function named "DelimitedSplit8K" I love that function.
The thing I am working on is that I need to split two different strings, place the now "rows" of data into table, each of their own.(Done)
Using a LEFT OUTER JOIN, select the rows that are in one table, and not in the other.(Need Assistance)
I know how to use the string splitter, and it works fine. But I am having trouble performing the LEFT OUTER JOIN.
CREATE TABLE #FirstDataString
(
FDataString varchar(250)
)
CREATE TABLE #SecondDataString
(
SDataString varchar(250)
)
INSERT INTO #FirstDataString
(
FDataString
)
VALUES
(
'1,3,6,8,9,10,12,13,15,16,20,21,24,26,28,31,33,34,35,49,51,52,53,57,58,61,63,66,69,70,71,75,76,77,78,79,81,82,85,88,90,91,92,93,94,95,96,97,98,99,100,105,107,110,111,112,118,122,124,125'
)
INSERT INTO #SecondDataString
(
SDataString
)
VALUES
(
'1,3,5,8,9,10,13,15,16,20,24,26,28,31,33,35,49,51,52,57,58,61,63,69,70,75,76,77,78,79,81,85,88,90,91,92,94,95,96,97,98,100,105,110,111,118,122,125'
)
SELECT Item AS FirstItem
FROM #FirstDataString f
CROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') split
SELECT Item AS SecondItem
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') split
DROP TABLE dbo.#FirstDataString;
DROP TABLE dbo.#SecondDataString;
I hope that someone can assist me with this query. I feel that I am close, but something is just not making it, and I cannot figure that part out.
Thank You in advance for all your assistance, comments and suggestions.
Andrew SQLDBA
October 9, 2013 at 2:56 pm
Misread OP. Comment removed.:blush:
-- Itzik Ben-Gan 2001
October 9, 2013 at 2:59 pm
Hi
A couple of other options, NOT EXISTS and FULL OUTER JOIN
CREATE TABLE #FirstDataString
(
FDataString varchar(250)
)
CREATE TABLE #SecondDataString
(
SDataString varchar(250)
)
INSERT INTO #FirstDataString
(
FDataString
)
VALUES
(
'1,3,6,8,9,10,12,13,15,16,20,21,24,26,28,31,33,34,35,49,51,52,53,57,58,61,63,66,69,70,71,75,76,77,78,79,81,82,85,88,90,91,92,93,94,95,96,97,98,99,100,105,107,110,111,112,118,122,124,125'
)
INSERT INTO #SecondDataString
(
SDataString
)
VALUES
(
'1,3,5,8,9,10,13,15,16,20,24,26,28,31,33,35,49,51,52,57,58,61,63,69,70,75,76,77,78,79,81,85,88,90,91,92,94,95,96,97,98,100,105,110,111,118,122,125'
)
SELECT Item AS FirstItem
FROM #FirstDataString f
CROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') splitf
WHERE NOT EXISTS (
SELECT Item AS SecondItem
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') splits
WHERE splitf.item = splits.item)
SELECT Item AS SecondItem
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') splits
WHERE NOT EXISTS (
SELECT Item AS FirstItem
FROM #FirstDataString f
CROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') splitf
WHERE splitf.item = splits.item)
SELECT fs.FirstItem, ss.SecondItem
FROM (
SELECT Item AS FirstItem
FROM #FirstDataString f
CROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') splitf) fs
FULL OUTER JOIN
(
SELECT Item AS SecondItem
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') splits) ss ON fs.FirstItem = ss.SecondItem
WHERE fs.FirstItem is null or ss.SecondItem is null
GO
DROP TABLE dbo.#FirstDataString;
DROP TABLE dbo.#SecondDataString;
October 9, 2013 at 6:45 pm
If you want to return a number if it appears in either table but not in the other, I don't think EXCEPT will work.
Not sure if my interpretation is correct, but if it is this will do it.
SELECT Item
FROM
(
SELECT Item
FROM #FirstDataString a
CROSS APPLY dbo.DelimitedSplit8k(FDataString,',')
UNION ALL
SELECT Item
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(SDataString,',')
) a
GROUP BY Item
HAVING COUNT(*) = 1;
This returns 14 rows whereas EXCEPT returns 13.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 10, 2013 at 2:06 pm
Thank You to everyone that gave their code, comments and suggestions. They all worked really well.
How would I add a bit column and fill it with a 1 if the values were in both tables, and a 0 if the Item was not in the first table?
Thank You in advance
Andrew SQLDBA
October 10, 2013 at 2:52 pm
On the FULL OUTER join query, a CASE statement would do it.
SELECT fs.FirstItem, ss.SecondItem,
CASE WHEN fs.FirstItem = ss.SecondItem THEN 1
WHEN fs.FirstItem is null THEN 0
ELSE null END flag
FROM (
SELECT Item AS FirstItem
FROM #FirstDataString f
CROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') splitf) fs
FULL OUTER JOIN
(
SELECT Item AS SecondItem
FROM #SecondDataString s
CROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') splits) ss ON fs.FirstItem = ss.SecondItem
--WHERE fs.FirstItem is null or ss.SecondItem is null
or if you want to get fancy:-) you could use
COALESCE(CAST(FirstItem + SecondItem as bit),CAST(FirstItem as bit) - 1)
Of course you would need to make sure the items are numbers for that one.
What would you do for items that are not in the second table?
October 10, 2013 at 3:09 pm
Thank You Micky
I was trying to use a CASE statement, but I had it in the wrong place.
Greatly appreciate your assistance
Andrew SQLDBA
October 10, 2013 at 3:15 pm
No problem ... glad to help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply