CODEWITHSUNDEEP
sqlsql-server-2016
js1229
js1229

Reputation: 25

Extract variable length substring from string

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

Upvotes: 2

Views: 990

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

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]

Upvotes: 3

Related Questions