select string between two ',' symbols

  • hello all.

    I have a Column called HID with varchar type.for example:'12,3,16'

    '16,10,256'

    '2,150,110,200'

    '5,70,4'

    I need to have this result:3 with int type

    10 with int type

    110 with int type

    70 with int type

    How can I have this with TSql?

  • Just in relation to this value

    '2,150,110,200'

    Why 110 and not 150,110?

    If you had '2,100,110,150,200,123,159602,1568'

    What would the correct answer be?

  • hello anthony.green.

    In your example,I need this result:159602

    in fact I always need second part from last:If I have this:14,18,110,120,300 I need this:120

  • Number of ways to do it

    create table #test(HID varchar (50))

    insert into #test values

    ('12,3,16'),

    ('16,10,256'),

    ('2,150,110,200'),

    ('5,70,4'),

    ('2,100,110,150,200,123,159602,1568')

    select

    hid,

    charindex(',',reverse(hid)) AS RightMostCommaPosition,

    left(hid,len(hid)-charindex(',',reverse(hid))) AS NewHID,

    charindex(',',reverse(left(hid,len(hid)-charindex(',',reverse(hid))))) AS NowRightMostCommaPosition,

    right(left(hid,len(hid)-charindex(',',reverse(hid))),charindex(',',reverse(left(hid,len(hid)-charindex(',',reverse(hid)))))-1) AS Result

    from #test

    ;with cte as

    (

    select * from #test

    cross apply dbo.DelimitedSplit8K (hid,',')

    )

    select t1.Item from cte t1

    inner join (select hid, max(itemnumber)-1 as MaxMin1 from cte group by hid) as t2

    on t1.hid = t2.hid and t1.ItemNumber = t2.MaxMin1

    drop table #test

    First two off the top of my head, others may chip in with other ways.

    Second you will need delimitedsplit8k from the "String Splitter" link in my signature.

  • Same as above but slightly different approach

    create table #test(HID varchar (50))

    insert into #test values

    ('12,3,16'),

    ('16,10,256'),

    ('2,150,110,200'),

    ('5,70,4'),

    ('2,100,110,150,200,123,159602,1568')

    Select *

    ,charindex(',',reverse(hid)) as last_comma

    ,LEN(HID) as string_length

    ,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String

    ,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma

    ,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1) As endResult

    FROm #test

  • Richie T (11/17/2015)


    Same as above but slightly different approach

    create table #test(HID varchar (50))

    insert into #test values

    ('12,3,16'),

    ('16,10,256'),

    ('2,150,110,200'),

    ('5,70,4'),

    ('2,100,110,150,200,123,159602,1568')

    Select *

    ,charindex(',',reverse(hid)) as last_comma

    ,LEN(HID) as string_length

    ,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String

    ,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma

    ,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1) As endResult

    FROm #test

    You need a final reverse on the EndResult to get it back into the correct order

  • insert into #test values

    ('2100110'),

    ('2100110,12')

    select

    HID,

    case when last_comma = 0 then HID

    when next_to_last_comma = 0 then LEFT(HID, LEN(HID) - last_comma)

    else SUBSTRING(HID, LEN(HID) - next_to_last_comma + 2, next_to_last_comma - last_comma - 1)

    end as next_to_last_value

    from #test

    cross apply (

    select charindex(',', reverse(HID)) as last_comma

    ) as assign_alias_names1

    cross apply (

    select charindex(',', reverse(HID), last_comma + 1) AS next_to_last_comma

    ) as assign_alias_names2

    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".

  • Good eyes 😉

    Select *

    ,charindex(',',reverse(hid)) as last_comma

    ,LEN(HID) as string_length

    ,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String

    ,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma

    ,reverse(SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1)) As endResult

    FROm #test

  • This is similar to the option previously posted by Anthony using the DelimitedSplit8k.

    WITH CTE AS

    (

    SELECT *, MAX(ItemNumber) OVER(PARTITION BY hid) - 1 AS thisItem

    FROM #test

    CROSS APPLY dbo.DelimitedSplit8K (hid,',')

    )

    SELECT Item

    FROM CTE

    WHERE ItemNumber = thisItem;

    If the maximum amount of items is 4, then a simpler approach is available.

    SELECT HID, PARSENAME( REPLACE( HID, ',', '.'), 2)

    FROM #test

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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