Error Handling for numbers of delimiters when extracting substrings

Situation: I have a column where each cell can have up to 5 delimiters. However, it’s possible that there are none.

Objective: How do i handle errors such as :

Invalid length parameter passed to the LEFT or SUBSTRING function.

in the case that it cannot find the specified delimiter.

Query:

declare @text VARCHAR(111) = 'abc-def-geeee-ifjf-zzz'
declare @start1 as int
declare @start2 as int
declare @start3 as int 
declare @start4 as int 
declare @start_index_reverse as int
set @start1 = CHARINDEX('-',@text,1)
set @start2 = CHARINDEX('-',@text,charindex('-',@text,1)+1)
set @start3 = CHARINDEX('-',@text,charindex('-',@text,CHARINDEX('-',@text,1)+1)+1)
set @start4 = CHARINDEX('-',@text,charindex('-',@text,CHARINDEX('-',@text,CHARINDEX('-',@text,1)+1)+1)+1)
set @start_index_reverse = CHARINDEX('-',REVERSE(@text),1)


select 
LEFT(@text,@start1-1) AS Frst,
SUBSTRING(@text,@start1+1,@start2-@start1-1) AS Scnd,
SUBSTRING(@text,@start2+1,@start3-@start2-1) AS Third,
SUBSTRING(@text,@start3+1,@start4-@start3-1)AS Third,
RIGHT(@text,@start_index_reverse-1) AS Lst

In this case my variable includes 5 delimiters and so my query works but if i removed one ‘-‘ it would break.

Answer

XML support in SQL Server brings about some unintentional but useful tricks. Converting this string to XML allows for some parsing that is far less messy than native string handling, which is very far from awesome.

DECLARE @test varchar(111) = 'abc-def-ghi-jkl-mnop'; -- try also with 'abc-def'

;WITH n(x) AS 
(
  SELECT CONVERT(xml, '<x>' + REPLACE(@test, '-', '</x><x>') + '</x>')
)
SELECT 
  Frst = x.value('/x[1]','varchar(111)'), 
  Scnd = x.value('/x[2]','varchar(111)'),
  Thrd = x.value('/x[3]','varchar(111)'),
  Frth = x.value('/x[4]','varchar(111)'),
  Ffth = x.value('/x[5]','varchar(111)')  
FROM n;

For a table it’s almost identical:

DECLARE @foo TABLE ( col varchar(111) );

INSERT @foo(col) VALUES('abc-def-ghi-jkl-mnop'),('abc'),('def-ghi');

;WITH n(x) AS 
(
  SELECT CONVERT(xml, '<x>' + REPLACE(col, '-', '</x><x>') + '</x>')
  FROM @foo
)
SELECT 
  Frst = x.value('/x[1]','varchar(111)'), 
  Scnd = x.value('/x[2]','varchar(111)'),
  Thrd = x.value('/x[3]','varchar(111)'),
  Frth = x.value('/x[4]','varchar(111)'),
  Ffth = x.value('/x[5]','varchar(111)')  
FROM n;

Results (sorry about the massive size, seems this doesn’t handle 144dpi well):