Split returned string into muliple columns

Question

I have a string containing a hierarchy of items separated by a '/' character.

E.g. Class\Order\Family\Genus\Species

I need to split out this string such that each value is its own column as well as displaying the original full string

E.g.

Mammalia\Carnivora\Felidae\Panthera\Panthera tigris  
Mammalia\Carnivora\Felidae\Panthera\Panthera leo  
Mammalia\Carnivora\Felidae\Panthera\Panthera pardus

becomes

Classification Class Order Family  Genus Species  
-------------- ----- ----- ------  ----- -------
Mammalia\Carnivora\Felidae\Panthera\tigris Mammalia  Carnivora Felidae Pathera tigris  
Mammalia\Carnivora\Felidae\Panthera\leo Mammalia  Carnivora Felidae Pathera leo  
Mammalia\Carnivora\Felidae\Panthera\pardus  Mammalia  Carnivora Felidae Pathera pardus

Finally, not all string will have 5 values so the script will need to enter NULL for values that don't exist

E.g.

Mammalia\Carnivora\Felidae

becomes

Classification Class Order Family  Genus Species  
Mammalia\Carnivora\Felidae Mammalia  Carnivora Felidae NULL NULL

score 0 · Answer 1 · answered Oct 04 '13 at 03:42

This should do what you want.

It uses common table expressions to split the string into parts (levels). Please not that I need to add \ to split it correctly so the input string shouldn't have \ at the end.

Then you just get the value for each level.

DECLARE @string NVARCHAR(500) = 'Mammalia\Carnivora\Felidae\Panthera\Panthera tigris'

;WITH cte
AS
(
    SELECT SUBSTRING(@string + '\', 1, CHARINDEX('\', @string, 1) - 1) AS Part, 
        SUBSTRING(@string + '\', CHARINDEX('\', @string, 1) + 1, LEN(@string + '\') - CHARINDEX('\', @string, 1) + 1) AS Remainder,
        0 AS Level

    UNION ALL

    SELECT SUBSTRING(cte.Remainder, 1, CHARINDEX('\', cte.Remainder, 1) - 1) AS Part, 
        SUBSTRING(cte.Remainder, CHARINDEX('\', cte.Remainder, 1) + 1, LEN(cte.Remainder) - CHARINDEX('\', cte.Remainder, 1) + 1) AS Remainder,
        cte.Level + 1 AS Level
    FROM cte
    WHERE CHARINDEX('\', cte.Remainder, 1) > 0
)

SELECT
    @string Classification,
    (SELECT Part FROM cte WHERE Level = 0) Class,
    (SELECT Part FROM cte WHERE Level = 1) [Order],
    (SELECT Part FROM cte WHERE Level = 2) Family,
    (SELECT Part FROM cte WHERE Level = 3) Genus,
    (SELECT Part FROM cte WHERE Level = 4) Species

Split returned string into muliple columns

1 Answers1