SQL Query Help - CHARINDEX

  • 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!

  • 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

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

  • 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, 3 months ago by  pietlinden.
  • 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

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

     

  • 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

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

  • 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.

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

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

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