SQL SERVER - How To Split Comma Separated String in to Separate records

Let us consider the temperatures of a city for one week and these are assigned to a String variable in the form of Comma Separated values as shown below.  

@Temperatures= ‘43,41,41,42,45,44,42’

Now we want to split these temperatures in to separate records. We can do this in many ways either by using Common Table Expression or by using the REPLACE() string function.

First Method : By using the Common Table Expression

Declare @Temperatures varchar(50)
Set @Temperatures= '43,41,41,42,45,44,42';

With Temperatures As(
Select Substring(@Temperatures,1,Charindex(',',@Temperatures)-1) FirstDay, Convert(Varchar(50), Substring(@Temperatures, Charindex(',',@Temperatures)+1, Len(@Temperatures))) NextDay
Union All
Select Substring(NextDay,1,Charindex(',',NextDay)-1) , Convert(varchar(50),Substring(NextDay,Charindex(',',NextDay)+1,Len(NextDay))+ ',') From Temperatures
Where Substring(NextDay,1,1)<> ',')

Select FirstDay Temperatures From Temperatures

Hmm..Seems to be a complicated code.

Ok, We will see another alternate method which is very simple.

Second Method : By Using the Replace() function.

Declare @SplitStr nvarchar(400)

Declare @Temperatures varchar(20)

Set @Temperatures= '43,41,41,42,45,44,42'  ;

Set @SplitStr = ' Select ''' +  Replace(@Temperatures, ',', ''' Temperatures UNION All Select ''') + ''''

Exec SP_ExecuteSql @SplitStr

Here I have used the Replace() function in order to replace the Comma with in the string with ' Union all Select '.

Finally, the end Result is :