SQL Query Help - CHARINDEX

Question

Post reply

SQL Query Help - CHARINDEX

SQL Server

Old Hand

Points: 377

July 20, 2020 at 7:24 pm

#3771488

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:

ExpectedOutput

Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

SQL Server Old Hand Points: 377 More actions · Answer 2

I am sorry. I should have been more clear. Actually I would like to DELETE the records which are NOT matching.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

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)
);

This reply was modified 4 years, 4 months ago by pietlinden.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

SQL Server wrote:

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

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;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Jason A. Long wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)