Friday, December 5, 2008

Splitting string with Delimiter in SQL

DECLARE @FullTask VARCHAR(100)
SET @FullTask = 'Task1-Task2'

If you know there would always be Task2, then the syntax for getting Task1 and Task2 is:
SELECT SUBSTRING(@FullTask, 1, CHARINDEX('-', @FullTask) - 1) AS [FirstTask],
SUBSTRING(@FullTask, CHARINDEX('-', @FullTask) + 1, LEN(@FullTask)) AS [LastTask]

If there is no LastTask, that is @FullTask='Task1' then getting LastTask would give error.

To avoid the above error, the query would be:
SELECT SUBSTRING(@FullTask, 1, NULLIF(CHARINDEX('-', @FullTask) - 1, -1)) AS [FirstTask],
SUBSTRING(@FullTask, CHARINDEX('-', @FullTask) + 1, LEN(@FullTask)) AS [LastTask]

This can be used for any delimiter, even if the delimiter is a space as ' ' instead of a hyphen as '-' in the above example.