0

I cannot use the split_string function and I cannot alter the database to set the compatibility level to what I need.

I have a column with the following example values:

JohnDoe-123-fakestreet-02-2018-01-01-2018-12-03
SamSmith-ab1-concordia-16-2018-02-02-2018-11-03
JaneDoe-zxy-austin-11-2018-03-01-2018-03-16
HomerSimpson-999-Chicago-22-2018-04-01-2018-10-20
EricCartman-222-Madison-05-2018-05-01-2018-09-01

I need the 4th value in each row. I.e.:

02
16
11
22
05

How can I do this? Any suggestions?

Chicken Sandwich No Pickles
  • 2,953
  • 8
  • 45
  • 92

2 Answers2

2
declare @TestData varchar(max) = 'JohnDoe-123-fakestreet-0299-2018-01-01-2018-12-03'

select @TestData
  , substring(
    @TestData
    , charindex('-', @TestData, charindex('-', @TestData, charindex('-', @TestData, 1)+1)+1)+1
    , (charindex('-', @TestData, charindex('-', @TestData, charindex('-', @TestData, charindex('-', @TestData, 1)+1)+1)+1)+1)
    - (charindex('-', @TestData, charindex('-', @TestData, charindex('-', @TestData, 1)+1)+1)+1) - 1
  )
Dale K
  • 25,246
  • 15
  • 42
  • 71
0

If all your values follow the same pattern as in your example strings, the last part of the string is fixed width. That means that you can just do:

select left(right(str, 24), 2)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • However the result he is looking for is not always 2 chars... – Dale K Dec 05 '18 at 03:04
  • 1
    @DaleBurrell . . . Interesting. It is in all the strings the OP shows. It isn't in the sample results. I wonder which is more accurate. I generally assume that zero-padded numbers are going to be fixed width. – Gordon Linoff Dec 05 '18 at 03:23
  • 1
    Looks like the results switch to using the second column... – Dale K Dec 05 '18 at 03:28