Extract variable length substring from string

Question

I am trying to extract a text string and break it out into multiple columns, but I'm running into issues as there are variable lengths.

Example:

SHP123-SLD2345-DIV67-CRP89999-SUP-1234124
SHP1234-SLD234-DIV678-CRP0987-SUP-012

I would want to break out the string into 5 columns; shp, sld, div, crp, sup

I also have a few odd rows with something like this;

 SHP12345-SLD23431-DIV4231-CRP432432-SUP-123-hello

the '123-hello' would still be considered a SUP.

The expected output would be something similar to this:

SHP123
SLD2345
DIV67
CRP89999
SUP-1234124

SHP1234
SLD234
DIV678
CRP0987
SUP-012

Show us sample data and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 21, 2017 at 16:12
Is this input data or stored data? Why is it in this format? — Josh Part
– Josh Part, Commented Mar 21, 2017 at 16:16
this is stored data. I'm not sure why it was stored in this format, but I do not have access to change the transactional system as that would be the ideal case. — js1229
– js1229, Commented Mar 21, 2017 at 17:54

Radu Gheorghiu · Accepted Answer · 2017-03-21 18:27:12Z

3

This should give you what you need:

declare @var nvarchar(max) = 'SHP12345-SLD2345987-DIV67-CRP89999-SUP-1234124'
--declare @var nvarchar(max) = '123-hello'

select
    @var
    , case when charindex('shp', @var, 0) <> 0
            then substring(@var, charindex('shp', @var, 0), charindex('-', @var, charindex('shp', @var, 0)) - charindex('shp', @var, 0)) 
        else NULL
        end [shp]
    , case 
        when charindex('sld', @var, 0) <> 0
            then substring(@var, charindex('sld', @var, 0), charindex('-', @var, charindex('sld', @var, 0)) - charindex('sld', @var, 0))
        else NULL
        end [sld]
    , case 
        when charindex('div', @var, 0) <> 0
            then substring(@var, charindex('div', @var, 0), charindex('-', @var, charindex('div', @var, 0)) - charindex('div', @var, 0))
        else NULL
        end [div]
    , case 
        when charindex('crp', @var, 0) <> 0
            then substring(@var, charindex('crp', @var, 0), charindex('-', @var, charindex('crp', @var, 0)) - charindex('crp', @var, 0))
        else NULL
        end [crp]
    , case 
        when charindex('sup', @var, 0) <> 0
            then substring(@var, charindex('sup', @var, 0), len(@var) + 1)
        else @var
    end [sup]

edited Mar 21, 2017 at 18:27

answered Mar 21, 2017 at 16:14

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Radu Gheorghiu Over a year ago

@js1229 You're welcome. Please don't forget to upvote / mark the answer as correct. Rewards keep me motivated to come back :)

js1229 Over a year ago

This worked well, but how do I also include those with more than 4 char after shp, sld, etc. to the query?

Radu Gheorghiu Over a year ago

@js1229 What do you mean? Can you update your question with more details?

js1229 Over a year ago

Excellent! Thank you

Collectives™ on Stack Overflow

Extract variable length substring from string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related