Posted by gondar |
Filed under substring, charindex
problem: we need to split the follwing string to cross the references or something else...
String input: Ref1|Ref2|Ref3|Ref4|Ref5|Ref6|Ref7|Ref8|Ref9|Ref10|Ref11|Ref12||||||||
Output we want:
ref1 - now that we have the first element we could insert it on a table or query the table to udpate blah blah blah...
ref2 - ...
ref3 - ...
ref4 - ...
...
so.. how we do this ?! simple :)
declare @string varchar(500),
@SpaceIndex TinyInt,
@temp varchar(500),
@val int
set @val=0
set @temp=''
set @string='Ref1|Ref2|Ref3|Ref4|Ref5|Ref6|Ref7|Ref8|Ref9|Ref10|Ref11|Ref12||||||||'
while @val=0
begin
if len(@string)>0
begin
SEt @SpaceIndex = CHARINDEX('|', @string)
if @SpaceIndex=0 and len(@string)>0
begin
SELECT @temp=@string set @val=1
end
else
begin
SELECT @temp=LEFT(@string, @SpaceIndex -1)
select @string=SUBSTRING(@string,len(@temp)+2,len(@string))
end
print('---> '+@temp)
end
else
set @val=1
end
78bcb32d-3920-417e-ad13-146ab39ef98f|0|.0