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

Today I was given a task to convert comma-separated numbers into comma-separated weekday names in SQL.

Today I was given a task  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 make one thing clear. In my table I have two columns, Days and SNO, SNO is identity column that will auto increment itself upon insertion of new rows  based on it I am looping along the rows. Whoever wants to use my code, ensure your table also has an identity column with the name 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 use 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

sql.jpg

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    

            BEGIN   

                SELECT  @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   

   

            END   

        SELECT  d.sno,t1.tDay,d.day   

        FROM    @mtab t1 JOIN DAYS d ON t1.sno=d.sno   

    END