SQL Query Help - CHARINDEX

  • SQL Server

    Old Hand

    Points: 362

    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!

  • Phil Parkin

    SSC Guru

    Points: 244732

    Try this:

    SELECT t1.*
    FROM #Test1 t1
    CROSS APPLY STRING_SPLIT(t1.SName, '/') ss
    JOIN #Test2 t2
    ON ss.value = t2.SName;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • SQL Server

    Old Hand

    Points: 362

    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: 62890

    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 1 month, 4 weeks ago by  pietlinden.
  • Phil Parkin

    SSC Guru

    Points: 244732

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jason A. Long

    SSC-Insane

    Points: 23711

    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: 244732

    Jason's solution should run faster, as long as there is never more than a single / in the t1 values.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeff Moden

    SSC Guru

    Points: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jason A. Long

    SSC-Insane

    Points: 23711

    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: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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