Post reply

last value from string

  • September 1, 2010 at 8:13 am

    #226441

    Declare @sss varchar(100)

    Set @sss ='12,11,23,34,567,690,4,1'

    or

    --- Set @sss ='45,56,89,212,121,33,14,7,101,4,22'

    or

    --- Set @sss ='334,234,217,1,4,333'

    How to select last value after comma?

    ( For example result from first string =1 , second =22 , third =333)

  • September 1, 2010 at 8:25 am

    #1215009

    You can use the REVERSE() Function, to help start from the end, and the charIndex, to find the comma. I added a case to ensure a comma is added at the end, for scenarios where there were none.

    SELECT REVERSE(SUBSTRING(REVERSE(@sss), 0,

    CHARINDEX(',', REVERSE(@sss) + ',', 0)))

    Cheers,

    J-F

  • September 1, 2010 at 11:52 am

    #1215192

    We can use left function also.

    Declare @sss varchar(100)

    Set @sss ='12,11,23,34,567,690,4,321'

    set @sss = reverse(@sss)

    set @sss = reverse(left(@sss,charindex(',',@sss) - 1))

    print @sss

  • September 1, 2010 at 2:11 pm

    #1215278

    Depending on how many of these you have to do, this may work better:

    select top (1) Item

    from dbo.DelimitedSplit8K(@sss, ',')

    order by ItemNumber desc;

    Click here for the latest Delimited Split Function.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 1, 2010 at 9:00 pm

    #1215375

    Just a suggestion... Doing 2 or 3 REVERSE's tends to be a bit expensive even compared to an ISNULL/NULLIF combo. If you're guaranteed to always have a comma, then you can remove the ISNULL/NULLIF combo from the following and still only need one REVERSE.

    DECLARE @sss TABLE (StringNumber INT, String VARCHAR(100))

    INSERT INTO @sss

    (StringNumber, String)

    SELECT 1, '12,11,23,34,567,690,4,1' UNION ALL

    SELECT 2, '45,56,89,212,121,33,14,7,101,4,22' UNION ALL

    SELECT 3, '334,234,217,1,4,333' UNION ALL

    SELECT 4, '1234' UNION ALL

    SELECT 5, '' UNION ALL

    SELECT 6, ','

    SELECT StringNumber,

    String,

    RIGHT(String,ISNULL(NULLIF(CHARINDEX(',',REVERSE(String))-1,-1),LEN(String)))

    FROM @sss

    --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)
    Intro to Tally Tables and Functions

  • September 3, 2010 at 12:49 pm

    #1216378

    change in the string - additional comma after last number.

    I need last value from string.

    Declare @sss varchar(100)

    Set @sss ='12,11,23,34,567,690,4,1,'

    or

    --- Set @sss ='45,56,89,212,121,33,14,7,101,4,22,'

    or

    --- Set @sss ='334,234,217,1,4,333,'

  • September 3, 2010 at 12:57 pm

    #1216384

    You should be able to figure this out... Charindex has an optional parameter, look it up in BOL.

    Cheers,

    J-F

  • September 3, 2010 at 1:34 pm

    #1216413

    J-F Bergeron (9/3/2010)

    You should be able to figure this out... Charindex has an optional parameter, look it up in BOL.

    Spot on.

    --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)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

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