devxlogo

Dealing with Delimited Strings in T-SQL

Dealing with Delimited Strings in T-SQL

This is a simple way to find out the word count in a comma delimited string:

 declare @mystring varchar(200)set @mystring="vb,asp,sqlserver,html"select (len(@mystring)-len(replace(@mystring,',',''))+1)

The following code will parse the delimited string:

 --variable i is for current and j for previous locationsDECLARE @mystring varchar(255), @myword varchar(50)DECLARE @i int,@j intSELECT @mystring = 'vb,asp,sqlserver,html'SELECT @i = 0,@j = 0IF SUBSTRING (@mystring, LEN (@mystring), 1)  ','BEGIN   SELECT @mystring = @mystring + ','ENDSELECT @i = CHARINDEX (',', @mystring, @i + 1)WHILE @i > 0BEGIN   SELECT @myword = SUBSTRING (@mystring, @j+1, (@i - @j) -1)   SELECT @myword   SELECT @j = @i   SELECT @i = CHARINDEX (',' , @mystring, @i + 1)END

devx-admin

Share the Post: