Increasing dilimited string not parsing correctly

Question

I am trying to break up a string coming in '!|!':

DECLARE @value varchar(max) = 'A750!|!none!|!none!|!N'
DECLARE @test varchar(max) = Replace(@value, '!|!', '.');

declare @my1 varchar(500) = (SELECT ParseName(@test, 1))
declare @my2 varchar(500) = (SELECT ParseName(@test, 2))
declare @my3 varchar(500) = (SELECT ParseName(@test, 3))
declare @my4 varchar(500) = (SELECT ParseName(@test, 4))
declare @my5 varchar(500) = (SELECT ParseName(@test, 5))
declare @my6 varchar(500) = (SELECT ParseName(@test, 6))
declare @my7 varchar(500) = (SELECT ParseName(@test, 7))

select @my1
select @my2
select @my3
select @my4
select @my5
select @my6
select @my7

Results:

But when I change the string to have another value:

DECLARE @value varchar(max) = 'A750!|!none!|!none!|!N!|!Y'

All NULLs return:

Why is this happening and how can I get more incoming separated values into variables?

score 2 · Answer 1 · answered May 14 '17 at 17:51

2

Parsename is supposed to be used for splitting the 4-part object name, not for generic string splitting -- and your script would totally break if the data contains ..

Just use a generic splitting function instead, for example DelimitedSplit8k by Jeff Moden

answered May 14 '17 at 17:51

James Z

12,209
10
24
44

Plus1 - my 1st thought as well. Everyone should have a good splitter – John Cappelletti May 14 '17 at 18:32

score 1 · Accepted Answer · answered May 14 '17 at 18:14

Just another option

Declare @value varchar(max) = 'A750!|!none!|!none!|!N'
Declare @my1 varchar(500) 
Declare @my2 varchar(500) 
Declare @my3 varchar(500) 
Declare @my4 varchar(500) 
Declare @my5 varchar(500) 
Declare @my6 varchar(500) 
Declare @my7 varchar(500) 

Select @my1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
      ,@my2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
      ,@my3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
      ,@my4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
      ,@my5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
      ,@my6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
      ,@my7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
From  (Select Cast('<x>' + replace((Select replace(@value,'!|!','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A

Increasing dilimited string not parsing correctly

2 Answers2