Splitting a SQL column's value into several fields

August 26, 2022

I have a bunch of rows that have a field called "name" that have values like this, delimited by a _:

ServerNumber_BrandNumber_BrandName_JobName

Sometimes, the job name will be spread out over two deliminations, like this:

ServerNumber_BrandNumber_BrandName_JobName_JobNamePart2

I want to break out each of those into their own field in a select statement like this:

SELECT
    name[0] as ServerNumber,
    name[1] as BrandNumber,
    name[2] as BrandName,
    name[3] as JobName
from table

If I do something like this it will work if job name is only part of one delmiter, but it will return nothing if it’s using two:

REVERSE(PARSENAME(REPLACE(REVERSE(name), '_', '.'), 1))

How can I do all of this?

>Solution :

Working example

Declare @YourTable Table ([Name] varchar(150))  Insert Into @YourTable Values 
 ('ServerNumber_BrandNumber_BrandName_JobName')
,('ServerNumber_BrandNumber_BrandName_JobName_JobNamePart2')
 

 Select Pos1 = JSON_VALUE(JS,'$[0]')
       ,Pos2 = JSON_VALUE(JS,'$[1]')
       ,Pos3 = JSON_VALUE(JS,'$[2]')
       ,Pos4 = concat(JSON_VALUE(JS,'$[3]'),'_'+JSON_VALUE(JS,'$[4]'))
 From  @YourTable A
 Cross Apply (values ('["'+replace(replace(string_escape([Name],'json'),' ','_'),'_','","')+'"]') ) B(JS)

Results

Pos1            Pos2        Pos3        Pos4
ServerNumber    BrandNumber BrandName   JobName
ServerNumber    BrandNumber BrandName   JobName_JobNamePart2

XML Approach (2012)

 Select Pos1 = xDim.value('/x[1]','varchar(150)')
       ,Pos2 = xDim.value('/x[2]','varchar(150)')
       ,Pos3 = xDim.value('/x[3]','varchar(150)')
       ,Pos4 = concat(xDim.value('/x[4]','varchar(150)'),'_'+xDim.value('/x[5]','varchar(150)'))
From  @YourTable A
Cross Apply ( values (cast('<x>' + replace((Select replace(NAME,'_','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml)))  B(xDim)