July 20, 2020 at 7:24 pm
Need query help.
create table #tblTest1
(ID int,
SName varchar(50),
Type varchar(10))
INSERT INTO #tblTest1 values (1,'abc/efg','OS')
INSERT INTO #tblTest1 values (2,'xyz/tgf','OS')
INSERT INTO #tblTest1 values (2,'tgf/xxx','OS')
INSERT INTO #tblTest1 values (3,'ccc/ppp','OS')
INSERT INTO #tblTest1 values (4,'ddd/tqm','OS')
INSERT INTO #tblTest1 values (5,'mpg/eee','OS')
create table #tblTest2
(SName varchar(50))
INSERT INTO #tblTest2 values ('efg')
INSERT INTO #tblTest2 values ('tgf')
INSERT INTO #tblTest2 values ('mpg')
I use this SQL to get the output. The SName in #tblTest1 can be before or after '/'. I am getting the output as expected, but wanted to see if there is any better method since I am using the IN statement 2 times here.
SELECT *
FROM #tblTest1
WHERE (
RIGHT(SName, CHARINDEX('/', REVERSE(SName)) - 1) IN (
SELECT SName
FROM #tblTest2
)
OR LEFT(SName, CHARINDEX('/', REVERSE(SName)) - 1) IN (
SELECT SName
FROM #tblTest2
)
)
Expected Output:
Thanks!
July 20, 2020 at 7:47 pm
Try this:
SELECT t1.*
FROM #Test1 t1
CROSS APPLY STRING_SPLIT(t1.SName, '/') ss
JOIN #Test2 t2
ON ss.value = t2.SName;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2020 at 8:09 pm
I am sorry. I should have been more clear. Actually I would like to DELETE the records which are NOT matching.
July 20, 2020 at 8:51 pm
Like this maybe?
DELETE
FROM #tblTest1
WHERE ID IN (
SELECT ID
FROM #tblTest1 t1
CROSS APPLY STRING_SPLIT(t1.SName, '/') ss
WHERE ss.value IN (SELECT SName FROM #tblTest2)
);
July 20, 2020 at 8:53 pm
I am sorry. I should have been more clear. Actually I would like to DELETE the records which are NOT matching.
Just need to switch things round a bit:
DELETE tst1
FROM #Test1 tst1
WHERE NOT EXISTS
(
SELECT *
FROM #Test1 t1
CROSS APPLY STRING_SPLIT(t1.SName, '/') ss
JOIN #Test2 t2
ON ss.value = t2.SName
WHERE t1.ID = tst1.ID
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 20, 2020 at 9:02 pm
There's and even easier way...
To answer the original post (finding the matches, you can use the following...
SELECT
t1.*
FROM
#tblTest1 t1
CROSS APPLY ( VALUES (CHARINDEX('/', t1.SName)) ) dp (div_pos)
CROSS APPLY ( VALUES (
SUBSTRING(t1.SName, 1, dp.div_pos - 1),
SUBSTRING(t1.SName, dp.div_pos + 1, 50)
) ) lr (left_val, right_val)
WHERE
EXISTS (SELECT 1 FROM #tblTest2 t2 WHERE t2.SName IN (lr.left_val, lr.right_val));
To rewrite it as a DELETE unmatched, you can do this...
DELETE t1
FROM
#tblTest1 t1
CROSS APPLY ( VALUES (CHARINDEX('/', t1.SName)) ) dp (div_pos)
CROSS APPLY ( VALUES (
SUBSTRING(t1.SName, 1, dp.div_pos - 1),
SUBSTRING(t1.SName, dp.div_pos + 1, 50)
) ) lr (left_val, right_val)
WHERE
NOT EXISTS (SELECT 1 FROM #tblTest2 t2 WHERE t2.SName IN (lr.left_val, lr.right_val));
SELECT * FROM #tblTest1 t1;
July 20, 2020 at 9:13 pm
Jason's solution should run faster, as long as there is never more than a single / in the t1 values.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2020 at 1:25 am
I've been through this more times than I can shake a stick at. With the utmost respect for those that provided them, none of the solutions offered are SARGable and they take a comparative shedload of CPU and logical reads to do the deed.
Save yourself a whole lot of time, aggravation, and frequently wrong results. Create two new computed columns that hold each side of the string, persist them, and add indexes to them. If you have more than 1 slash in the column, then we can cheat like hell but let's not go there unless we need to.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2020 at 7:51 pm
In addition to Jeff's good comments, another (and in my own opinion better) solution would be to normalize the table and split that column into a completely different table, with a separate row for each value.
July 22, 2020 at 12:26 am
In addition to Jeff's good comments, another (and in my own opinion better) solution would be to normalize the table and split that column into a completely different table, with a separate row for each value.
Spot on. If the table only has the 3 columns shown, then it could all live in the same table.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply