Convert Comma-Separated Numbers Into Comma-Separated Weekday Name in SQL

Introduction

Today I was tasked to convert comma-separated numbers into comma-separated weekday names in SQL.

For example

1,4,5 -MON, THU, SAT

2,7 -TUE, SUN

I have written the following procedure to do this, but let me clarify one thing. In my table, I have two columns, Days and SNO; SNO is an identity column that will auto increment itself upon inserting new rows. Based on it, I am looping along the rows. Whoever wants to use my code, ensure your table has an identity column named Sno.

My logic is very simple fetch the Days column data into a temporary variable, split the data on ','(comma)  loop along the splitted list using DATENAME and DW functionality of SQL to get datename.

Table script

USE [DEVDB]

GO

/****** Object:  Table [dbo].[Days]    Script Date: 03/08/2013 11:24:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Days](
      [Sno] [bigint] IDENTITY(1,1) NOT NULL,
      [Day] [varchar](500) NULL
) ON [PRIMARY] 
GO 

SET ANSI_PADDING OFF
GO

Procedure Script

CREATE PROCEDURE ConvertDays AS BEGIN DECLARE @mtab TABLE ( sno BIGINT,tDay VARCHAR(500) ) DECLARE @sno BIGINT DECLARE @count BIGINT DECLARE @rawstr VARCHAR(500) DECLARE @finalstr VARCHAR(500) DECLARE @days VARCHAR(500)
SELECT @days = 
    (SELECT '' ) DECLARE @finalDays VARCHAR(500) DECLARE @tempdays VARCHAR(500)
SELECT @count = 
    (SELECT COUNT(*)
    FROM DAYS )
SELECT * INTO #t1
FROM DAYS DECLARE @tcharindex BIGINT WHILE @count > 0 BEGINSELECT @sno = 
    (SELECT TOP ( 1 ) sno
    FROM #t1 )
SELECT @rawstr = 
    (SELECT #t1.DAY
    FROM #t1
    WHERE sno = @sno ) SET @rawstr = @rawstr + ','SELECT @tcharindex = 
    (SELECT CHARINDEX(',',
		 @rawstr) ) WHILE @tcharindex <> 0 BEGIN SET @finalstr = 
    (SELECT SUBSTRING(@rawstr,
		
		 0,
		
		 @tcharindex) ) SET @rawstr = 
        (SELECT SUBSTRING(@rawstr,
		 @tcharindex + 1,
		 LEN(@rawstr)) )
        SELECT @tcharindex = 
        (SELECT CHARINDEX(',', @rawstr) )
    SELECT @finalDays=
    (SELECT DATENAME(DW,
		CAST(@finalstr-1 AS INT)))
    SELECT @days= (@days+ ',' + 
        (SELECT SUBSTRING(@finalDays,
		1,
		3 )) )
        	END INSERT INTO @mtab VALUES (@sno,
		SUBSTRING(@days,
		2,
		LEN(@days))) SET @days = '' DELETE
FROM #t1
WHERE sno = @sno SET @count = @count - 1
	ENDSELECT d.sno,
		t1.tDay,
		d.day
FROM @mtab t1
JOIN DAYS d
	ON t1.sno=d.sno
	END