none
Extract string from column RRS feed

  • Question

  • Hi,

    I want to extract string. kindly help...

    SI/TLCK/3371
    SI/SDR/3668
    SI/KRM/4770
    SI/NLR2/4798
    SI/NLR2/4811
    SI/GNT6/4973
    SI/HMNR/5043
    SI/ANTP/5775

    string to be extracted is middle text


    GSKR

    Saturday, November 9, 2019 9:35 AM

Answers

  • Try this:

    create table #temp(col1 varchar(max))
    insert #temp values('SI/TLCK/3371'),('SI/SDR/3668'),('SI/KRM/4770'),('SI/NLR2/4798'),('SI/NLR2/4811'),('SI/GNT6/4973'),('SI/HMNR/5043'),('SI/ANTP/5775')

    select isnull(stuff(substring(col1,charindex('/',col1)+1,len(col1)),
                charindex('/',substring(col1,charindex('/',col1)+1,len(col1))),
                len(col1),NULL),
    substring(col1,charindex('/',col1)+1,len(col1)))
    from #temp

    This is exactly same problem with another user wanted to extract second part of a string using delimiter -

    Mark as answer if it helps. Good luck.


    • Edited by Soumen Barua Saturday, November 9, 2019 9:45 AM
    • Marked as answer by GSKR Saturday, November 9, 2019 9:47 AM
    Saturday, November 9, 2019 9:45 AM

All replies

  • Try this:

    create table #temp(col1 varchar(max))
    insert #temp values('SI/TLCK/3371'),('SI/SDR/3668'),('SI/KRM/4770'),('SI/NLR2/4798'),('SI/NLR2/4811'),('SI/GNT6/4973'),('SI/HMNR/5043'),('SI/ANTP/5775')

    select isnull(stuff(substring(col1,charindex('/',col1)+1,len(col1)),
                charindex('/',substring(col1,charindex('/',col1)+1,len(col1))),
                len(col1),NULL),
    substring(col1,charindex('/',col1)+1,len(col1)))
    from #temp

    This is exactly same problem with another user wanted to extract second part of a string using delimiter -

    Mark as answer if it helps. Good luck.


    • Edited by Soumen Barua Saturday, November 9, 2019 9:45 AM
    • Marked as answer by GSKR Saturday, November 9, 2019 9:47 AM
    Saturday, November 9, 2019 9:45 AM
  • Thank You... it worked....

    GSKR

    Saturday, November 9, 2019 9:47 AM
  • create table test(col varchar(50))
    insert test values('SI/TLCK/3371')
    ,('SI/SDR/3668')
    ,('SI/KRM/4770')
    ,('SI/NLR2/4798')
    ,('SI/NLR2/4811')
    ,('SI/GNT6/4973')
    ,('SI/HMNR/5043')
    ,('SI/ANTP/5775')
    
    
    select Parsename(replace(col,'/','.'),2)
    from test
    
    drop table test

    Sunday, November 10, 2019 2:15 AM
    Moderator