Splitting text by using comma separator in mssql query

By sri sri

Access over 40 UI widgets with everything from interactive menus to rich charts.

If we want to split text using a comma separator, for example consider the text "field1,field2,field3" it needs to be split in the mssql query. To achieve that it can be done by using "CharINDEX" in T-SQL.

Try the below query

declare @fieldName as nvarchar(100)
declare @concat as nvarchar(100)
declare @pos as int
set @fieldName = 'field1,field2,field3,field4,field5,field6,field7,field1,field2,field3,field4,field5,field6,field7'
declare @flag as int
if (@fieldName is null or @fieldName='')
   set @flag=0
else
   set @flag=1
while (@flag=1)
   Begin
       set @pos = charINDEX(',',@fieldName)
       if (@pos=0)
           Begin
               select @concat = @fieldName
               set @fieldName=''
           End
       else
           Begin
               select @concat = substring(@fieldName,0,@pos)
           End

       print(@concat)
       set @fieldName = substring(@fieldName,@pos+1,len(@fieldName))
       if (@fieldName is null or @fieldName='')
           set @flag=0
       else
           set @flag=1
   End

Popularity (1814 Views)

Biography - sri sri

asp.net c# sql server