Extract variable length substring from string
I am trying to extract a text string and split it into multiple columns, but I am running into problems as there are variable lengths.
Example:
SHP123-SLD2345-DIV67-CRP89999-SUP-1234124 SHP1234-SLD234-DIV678-CRP0987-SUP-012
I would like to split a row into 5 columns; shp, sld, div, crp, sup
I also have some odd lines with something like this:
SHP12345-SLD23431-DIV4231-CRP432432-SUP-123-hello
"123-hello" will still count as SUP.
The expected output will be something like this:
SHP123
SLD2345
DIV67
CRP89999
SUP-1234124
SHP1234
SLD234
DIV678
CRP0987
SUP-012
+3
source to share
1 answer
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]
+3
source to share