0

suppose I have following sql code 

DECLARE @colsLoop AS NVARCHAR(MAX) ='[Khulna Titans],[Rajshahi Kings]';
declare @pos as numeric = 0;
declare  @len as numeric = 0;
declare @value as varchar(255);


WHILE CHARINDEX(',',  @colsLoop, @pos+1)>0

BEGIN
set @len = CHARINDEX(',', @colsLoop, @pos+1) - @pos
set @value = SUBSTRING(@colsLoop, @pos, @len)

print @value

set @pos = CHARINDEX(',', @colsLoop, @pos+@len) +1
end

I cant figure it out why this code only print [Khulna Titans] , I want it print [Rajshahi Kings] after [Khulna Titans], in other words why loop runs only once. Where do I mistake?

Eugene Lisitsky
  • 12,113
  • 5
  • 38
  • 59
Agahii
  • 39
  • 1
  • 8
  • 1
    Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – Pred Nov 27 '17 at 16:47

1 Answers1

0

The reason your loop stops is because the loop processes once for each comma found in the string. You have only one comma in the string, so it processes just the first item. If you want it to process both strings you would need to add a comma to the end of the string.

Or print the remaining portion of the string after you exit the loop if it is not possible to add a comma to the end of the string.

hth

xDJR1875
  • 280
  • 1
  • 18