Getting the data one by one from a comma separated string using SQL Query

In this article we will see how to separate data one by one from a comma separated string.

First declare a variable to store articles in comma separated values.

DECLARE @DataString VARCHAR(200)
SET @DataString = 'ASP.NET, VB.NET, C#.NET, SQL, Javascript'

Add comma at last to @DataString

SET @DataString = @DataString + ','

Using while loop through all the topics in @DataString

WHILE (CHARINDEX(',', @DataString) > 0) 
--
CHARINDEX is a SQL inbuilt function which returns the integer value if charecter is present in a string.
BEGIN
        -- To hold each topic
        DECLARE @Topics VARCHAR(30)
        -- Get the topic one at a time           
        SET @Topics = SUBSTRING(@DataString,0, CHARINDEX(',', @DataString))
        PRINT @Topics
       
        -- Reset the @DataString
        SET @DataString = SUBSTRING(@DataString, CHARINDEX(',', @DataString) + 1, LEN(@DataString))

END

Here is the out put of the above query

 
ASP.NET
 VB.NET
 C#.NET
 SQL
 Javascript


 

By Cos mos   Popularity  (2098 Views)