How to Retrieve a Substring From a Larger String of Varying Length

  • Polymorphist

    SSCommitted

    Points: 1904

    Hello -

    I have a unique problem - I have a requirement to retrieve a substring which appears between the fourth and fifth underscores in a string.  The parent string varies in length, so I actually have to do a bunch of charindexes on the underscores to get what I need.  I'm having a hell of a time trying to do this.

    Following is an example of the string -

    [0107602_000002008823_20150206_1_449589981255609050_20150206__20.26]

    I need to retreive the numbers between the fourth and fifth underscores.

    Here is another example string -

    [0111904_000001054979_20131113_1_69413497_20131113__30.24]

    The strings can vary in length which is making this more difficult.

    Can anyone help?

  • pietlinden

    SSC Guru

    Points: 62342

    Use DelimitedSplit8K() and then specify the item# you want to return?

    Jeff Moden wrote an article about splitting strings and included a UDF to do it. It returns (Value, Position) so you could do something like (from memory... I'm reinstalling SQL Server right now...)

    SELECT ca.Item, ca.Position

    FROM MyTable t

    CROSS APPLY DelimitedSplit8K(t.FieldToSplit,',') ca

    WHERE ca.Position = 4

    So I'm sure my code needs a bit of tweaking, but hopefully you get the idea.

  • pietlinden

    SSC Guru

    Points: 62342

    (Why can't I edit my original response? Hmm...)

    Here's the article:

    https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')

    FROM #JBMTest test

    CROSS APPLY dbo.DelimitedSplit8k(test.SomeValue,',') split

    WHERE split.ItemNumber = 4;

  • Polymorphist

    SSCommitted

    Points: 1904

    Thanks for the help, i'll take a look.

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

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