Error Handling for numbers of delimiters when extracting substrings

Question

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.

If a row-per-token instead of column-per-token is usable you can use a recursive cte for this? — Alex K., Apr 03 '19 at 16:32
Would the solution I posted [here](https://stackoverflow.com/questions/55476624/re-iterate-string-extraction-for-delimited-string/55478736#55478736) work for you? — Luis Cazares, Apr 03 '19 at 16:52
@AaronBertrand yes thats exactly what im looking for. using 2012. — Roger Steinberg, Apr 03 '19 at 18:57

Aaron Bertrand · Accepted Answer · 2019-04-03T20:50:26.363

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):

now if I wanted to replace the @test to a column from a table. how would i do that? — Roger Steinberg, Apr 03 '19 at 20:08
@Roger well, if that’s the problem you’re solving, that’s what you should lead with. You replace `@test` with the column name and add FROM inside WITH. If you post your real problem, the real table structure, some sample data that represents both the example in the question and edge cases, and desired output, then we can solve your actual problem the first time. :-) — Aaron Bertrand, Apr 03 '19 at 20:19

score 0 · Answer 2 · answered Apr 03 '19 at 16:22

add a test before your last select then you should decide how to handle the other case (when one of start is 0) You can also refer to this link about splitting a string in sql server which is uses a loop and can handle any number of delimiters

if @start1>0 and @start2>0 and @start3>0 and @start4>0
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

Error Handling for numbers of delimiters when extracting substrings

2 Answers2