Split string at each occurrence of delimiter

I need to split this string into multiple fields at the occurrence of each delimiter as shown below:

Display Value
466500-GO-INF-ITAPPS-EMP-CLERADM

Main Account  Business Unit   Department Cost Center   Asset Type   Classification
466500        GO              INF        ITAPPS        EMP          CLERADM

I currently am using the query below to remove the characters before the "-":

stuff([DISPLAYVALUE], 1, charindex('-', [DISPLAYVALUE]), '')

I then execute this query to return the characters before the "-":

case

when charindex('-',[DISPLAYVALUE])>0

then Substring([DISPLAYVALUE], 1, Charindex('-', [DISPLAYVALUE])-1)

else [DISPLAYVALUE]

end

Unfortunately this results in multiple helper columns. Is there a way to achieve this solution in one query?

You can achieve the desired output converting your string to XML and then taking advantage of XML type methods such as VALUE (more info here) to extract the information you need:

DECLARE @mockup TABLE ([DISPLAYVALUE] VARCHAR(100));

INSERT INTO @mockup
VALUES ('466500-GO-INF-ITAPPS-EMP-CLERADM')

;WITH Splitted
AS (
    SELECT  
         CAST('<x>' + REPLACE([DISPLAYVALUE], '-', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @mockup
    )
SELECT
     Parts.value(N'/x[1]', 'int')         as [Main Account]
    ,Parts.value(N'/x[2]', 'varchar(50)') as [Business Unit]
    ,Parts.value(N'/x[3]', 'varchar(50)') as [Department]
    ,Parts.value(N'/x[4]', 'varchar(50)') as [Cost Center]
    ,Parts.value(N'/x[5]', 'varchar(50)') as [Asset Type]
    ,Parts.value(N'/x[6]', 'varchar(50)') as [Classification]
FROM Splitted;

Result:

The first select statement:

SELECT  
     CAST('<x>' + REPLACE([DISPLAYVALUE], '-', '</x><x>') + '</x>' AS XML) AS Parts
FROM @mockup

converts your string into an XML document with a sequence of XML tags (named <x>, but you could have used anything else here), each of them containing one of your values:

<x>466500</x>
<x>GO</x>
<x>INF</x>
<x>ITAPPS</x>
<x>EMP</x>
<x>CLERADM</x>

Now in the second select statement you can use specific XML type operators such as VALUE() to get each value from the XML and put it in a separate column:

SELECT
     Parts.value(N'/x[1]', 'int')         as [Main Account]
    ,Parts.value(N'/x[2]', 'varchar(50)') as [Business Unit]
    ,Parts.value(N'/x[3]', 'varchar(50)') as [Department]
    ,Parts.value(N'/x[4]', 'varchar(50)') as [Cost Center]
    ,Parts.value(N'/x[5]', 'varchar(50)') as [Asset Type]
    ,Parts.value(N'/x[6]', 'varchar(50)') as [Classification]
FROM Splitted;

here you are using an XQuery to extract a single value from the XML document created in the first select.

For example to extract the content of the first tag <x> you use the XQuery '/x[1]' to retrieve the first element, '/x[2]' for the second and so on.