0

What SQL syntax would split this column into three new columns (split would be on the underscore)?

enter image description here

There are other columns in the table, but I would like to split these into three (underscore based).

0
2

i usually say to people don't save data with any delimiter

CREATE TABLE [test] (JOBNUMBR VARCHAR(15))
GO
INSERT INTO test SELECT '57299_87961_003' UNION SELECT '66515_88567_OOO' UNION SELECT '58OO7_88695_OO1'
GO
SELECT JOBNUMBR
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 1)) As [no1]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 2)) As [no2]
,Reverse(ParseName(Replace(Reverse(JOBNUMBR), '_', '.'), 3)) As [no3]
FROM (Select newid() AS [ID], JOBNUMBR from test) As [t]
GO
JOBNUMBR        | no1   | no2   | no3
:-------------- | :---- | :---- | :--
57299_87961_003 | 57299 | 87961 | 003
58OO7_88695_OO1 | 58OO7 | 88695 | OO1
66515_88567_OOO | 66515 | 88567 | OOO

db<>fiddle here

2

If your data is XML compliant, you can get a little more flexible by making it look like XML, then getting the data from there:

WITH V AS (
    SELECT * FROM (
        VALUES 
            ('123_456_789')
            ,('123_ABC_789')
            ,('1_4_7')

    ) [Values](ToSplit)

)
SELECT 
    q.x.value('d[1]', 'varchar(5)') A ,   
    q.x.value('d[2]', 'varchar(5)') B ,   
    q.x.value('d[3]', 'varchar(5)') C  
FROM V
    CROSS APPLY (
        SELECT CAST(CONCAT('<d>', REPLACE(ToSplit, '_', '</d><d>' ), '</d>') AS XML) x
    )  Q

OR

WITH V AS (
    SELECT * FROM (
        VALUES 
            ('123_456_789')
            ,('123_ABC_789')
            ,('1_4_7')

    ) [Values](ToSplit)

),
 Q as (
    SELECT CAST(CONCAT('<d>', REPLACE(ToSplit, '_', '</d><d>' ), '</d>') AS XML) x FROM V

 )
SELECT 
    x.value('d[1]', 'varchar(5)') A ,   
    x.value('d[2]', 'varchar(5)') B ,   
    x.value('d[3]', 'varchar(5)') C  
FROM Q

Both result in

A     B     C
----- ----- -----
123   456   789
123   ABC   789
1     4     7

The CTE V is just to setup the test data. You start by wrapping you data in a starting and ending tag, then replace your delimiter with an ending + starting tag. Convert all of this to XML, then get the value of the node ordinally.

Not the answer you're looking for? Browse other questions tagged or ask your own question.