Find the second occurrence in the string

  • I have standard data as

    C1588-27-3
    C1588-28-3
    C1588-29-8

    I want result as  in SQL as

    1588-27
    1588-28
    1588-29

    I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
    it always give me 0 so try with  PATINDEX that works but it give mefirst occurrence i want second occurrence is any idea how this will work. Appreciateyour help.

    CHARINDEX giving 0 instead of 6 characters
    SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')

  • Drop Table If Exists #Temp
    Create Table #Temp
    (
     Code VarChar(20)
    )

    Insert Into #Temp(Code)
    Values
    ('C1588-27-3'),
    ('C1588-28-3'),
    ('C1588-29-8')

    Select Code, SubString(Code, 2, SecondHypen - 2)
    From #Temp
    Cross Apply (Select CharIndex('-', Code, CharIndex('-', Code) + 1) As SecondHypen) S

  • Thanks it worked like charm!!!

  • sks_989 - Thursday, August 2, 2018 5:56 PM

    I have standard data as

    C1588-27-3
    C1588-28-3
    C1588-29-8

    I want result as  in SQL as

    1588-27
    1588-28
    1588-29

    I tried replacefirst character REPLACE('C1588-27-3','C','') it works then i want to usesubstring so try to find CHARINDEX
    it always give me 0 so try with  PATINDEX that works but it give mefirst occurrence i want second occurrence is any idea how this will work. Appreciateyour help.='font-variant-ligatures:>

    CHARINDEX giving 0 instead of 6 characters
    SELECT PATINDEX('%-%', 'C1588-27-3'), CHARINDEX('C1588-27-3','-')

    Is the format and segment lengths of the data always the same?
    😎

  • andycadley - Thursday, August 2, 2018 7:42 PM

    Drop Table If Exists #Temp
    Create Table #Temp
    (
     Code VarChar(20)
    )

    Insert Into #Temp(Code)
    Values
    ('C1588-27-3'),
    ('C1588-28-3'),
    ('C1588-29-8')

    Select Code, SubString(Code, 2, SecondHypen - 2)
    From #Temp
    Cross Apply (Select CharIndex('-', Code, CharIndex('-', Code) + 1) As SecondHypen) S

    This will  work only if you 2nd occurrence of string 

    Drop Table If Exists #Temp
    Create Table #Temp
    (
    Code VarChar(20)
    )

    Insert Into #Temp(Code)
    Values
    ('C1588-27-3'),
    ('C1588-28-3'),
    ('C1588-29-8');
    Solution:
    select substring(Code,1,(charindex('-',code)+charindex('-',reverse(code))))
    from #Temp;

    Saravanan


  • Select Code, new_string
    FROM #Temp
    CROSS APPLY (
        SELECT STUFF(Code, 1, 1, '') AS base_string
    ) AS ca1
    CROSS APPLY (
        SELECT LEFT(base_string, CHARINDEX('-', base_string,
            CHARINDEX('-', base_string) + 1) - 1) AS new_string
    ) AS ca2

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

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