0

I have a field in the following format: 'xyz;123;abc' or '456;bcd' and would like to extract just the second last string between the ';'s. In these example, I want the new column to be '123' and '456'.

Dale K
  • 25,246
  • 15
  • 42
  • 71
SQLUser
  • 113
  • 8
  • Possible duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) – Syntax Error Sep 27 '18 at 13:58
  • If you are lucky to be using SQL Server 2016 then you can simply use SPLIT() – Ross Bush Sep 27 '18 at 13:59
  • @RossBush STRING_SPLIT doesn't work as op wants – Lamak Sep 27 '18 at 14:01
  • If you use a splitter I would strongly advise you NOT to use the one from the accepted answer in the proposed duplicate. Looping is not needed for splitting strings. But honestly a splitter seems excessive here. – Sean Lange Sep 27 '18 at 14:01
  • @RossBush it's `STRING_SPLIT`, however, that won't help; the function doesn't provide oridinal position. Personally I recommend [DelimitedSplit8k](http://www.sqlservercentral.com/articles/Tally+Table/72993/), or [DelimtiedSplit8K_LEAD](http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/) if you're on SQL Server 2012+ if you need a string splitter, and need to retain or (reliably) retain the order of the items. – Thom A Sep 27 '18 at 14:01
  • @Lamak - Thanks for the update. – Ross Bush Sep 27 '18 at 14:09
  • @Larnu - I could not find the link, however, there is a widely read article comparing the fastest string split functions. Thanks for the info. – Ross Bush Sep 27 '18 at 14:10

2 Answers2

4

Assuming you always have at least two values you can use PARSENAME for this which is a lot simpler than a string splitter or complicated string manipulation.

declare @Something table
(
    SomeVal varchar(20)
)

insert @Something values
('xyz;123;abc')
, ('456;bcd')

select *
    , parsename(replace(s.SomeVal, ';', '.'), 2)
from @Something s
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
1

reverse the string, find the first and second ; and you got the string you want. Just reverse again to get it in the right order

; with tbl as
(
    select  field = 'xyz;123;abc'   union all
    select  field = '456;bcd'
)
select  *, new_col = reverse(substring(reverse(field), idx1 + 1, idx2 - idx1 - 1))
from    tbl t
    cross apply
    (
        select  idx1 = charindex(';', reverse(field))
    ) s1
    cross apply
    (
        select  idx2 = charindex(';', reverse(field) + ';', idx1 + 1)
    ) s2
Squirrel
  • 23,507
  • 4
  • 34
  • 32