Post reply

How to get the particular value in the String which has delimeters

  • September 14, 2007 at 11:13 am

    #153825

    I have ntext column which has a String value like this /pna/v3/pg/product/manual/display/0,,2076_15221_49434,00.HTML.

    need to get the integer after the last underscore(_) and before the string ,00.html.In this case output should be 49434.

  • September 14, 2007 at 11:52 am

    #733854

     

    --sql doesnt have an instrRev function, but separate functions you can combine to do the same thing:

    --so you have to use substring and reverse:

    declare @str varchar(1024)

    set @str = '/pna/v3/pg/product/manual/display/0,,2076_15221_49434,00.HTML.

    '

    --step by step so you can see the process:

    SELECT REVERSE(@str)  --   .LMTH.00,43494_12251_6702,,0/yalpsid/launam/tcudorp/gp/3v/anp/

    --find substring until that charindex of underscore

    --we need just the string portion: everthing right of question mark

    select substring(REVERSE(@str),1,charindex('_',REVERSE(@str))-1 ) --   .LMTH.00,43494

    --finally reverse it back to original format

    select REVERSE(substring(REVERSE(@str),1,charindex('_',REVERSE(@str))-1 )  ) --49434,00.HTML.  

    --now get just the part before teh comma(this assumes there is ALWAYS a comma?

    --select substring(x,1,charindex(',',x)

    select substring(REVERSE(substring(REVERSE(@str),1,charindex('_',REVERSE(@str))-1 )  ),1,charindex(',',REVERSE(substring(REVERSE(@str),1,charindex('_',REVERSE(@str))-1 )  )) -1)  --49434

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 14, 2007 at 12:08 pm

    #733858

    Thanks buddy I will try your idea

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

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