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