Updating database column with string built based on value of another column

Question

I have a table with a column called Days. The Days column stores a comma delimited string representing days of the week. For example the value 1,2 would represent Sunday, Monday. Instead of storing this information as a comma delimited string, I want to convert it to JSON and store it in a column called Frequency in the same table. For example, a record with the Days value of 1,2 should be updated to store the following in it's Frequency column:

'{"weekly":"interval":1,"Sunday":true,"Monday":true,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}'

I found a way to do this using a case statement assuming that there is only one digit in the Days column like so:

UPDATE SCH_ITM 
SET
    FREQUENCY = 
        CASE 
        WHEN SCH_ITM.DAYS = 1 THEN '{"weekly":{"interval":1,"Sunday":true,"Monday":false,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 2 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":true,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 3 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":false,"Tuesday":true,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 4 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":false,"Tuesday":false,"Wednesday":true,"Thursday":false,"Friday":false,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 5 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":false,"Tuesday":false,"Wednesday":false,"Thursday":true,"Friday":false,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 6 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":false,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":true,"Saturday":false}}' 
        WHEN SCH_ITM.DAYS = 7 THEN '{"weekly":{"interval":1,"Sunday":false,"Monday":false,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":true}}' 
        END
WHERE SCH_TYPE = 'W';

However I cannot seem to figure out an effecient way to handle converting a value such as 1,5 into the correct JSON representation. Obviously I could write out every possible permutation, but surely is a better way?

I don't have an answer for you but I wonder why you wouldn't use DATEPART() and avoid the column all together. Maybe you don't have a date column, but I was just wondering. — S3S
– S3S, Commented Nov 11, 2016 at 16:26

James Casey · Accepted Answer · 2016-11-11 16:44:08Z

Okay this will give you what you have asked for

create table test (days varchar(20), frequency varchar(500))

insert into test(days) values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('1,5')

update test set frequency = '{"weekly":{"interval":1,'
    + '"Sunday": ' + case when days like '%1%' then 'true' else 'false' end + ','
    + '"Monday": ' + case when days like '%2%' then 'true' else 'false' end + ','
    + '"Tuesday": ' + case when days like '%3%' then 'true' else 'false' end + ','
    + '"Wednesday": ' + case when days like '%4%' then 'true' else 'false' end + ','
    + '"Thursday": ' + case when days like '%5%' then 'true' else 'false' end + ','
    + '"Friday": ' + case when days like '%6%' then 'true' else 'false' end + ','
    + '"Saturday": ' + case when days like '%7%' then 'true' else 'false' end + '}}'

select * from test

Though of course e.g. Days = '1234' will produce the same as '1,2,3,4' - as will 'Bl4arg3le12' for that matter. If Days is a string, you can put '8' which is meaningless?

Really it sounds like you need an extra table or two:

If "MyTable" is the table with the Days column, add a Days table with the days of the week, then a MyTableDays table to link MyTable entries to days - for the 1,5 example, there would be two rows in MyTableDays

John Cappelletti · Accepted Answer · 2016-11-11 16:39:41Z

With the help of a parse function and an cross apply

;with cteDays As (Select ID,Name From (Values(1,'Sunday'),(2,'Monday'),(3,'Tuesday'),(4,'Wednesday'),(5,'Thursday'),(6,'Friday'),(7,'Saturday')) D(ID,Name))

Update YourTable Set Frequency =  '{"weekly":"interval":1,'+String+'}}'
 From  YourTable A
 Cross Apply (
              Select String = Stuff((Select ','+String 
              From (
                    Select String='"'+Name+'":'+case when RetVal is null then 'false' else 'true' end
                    From   [dbo].[udf-Str-Parse](A.Days,',') A
                    Right Join cteDays B on RetVal=ID) N
              For XML Path ('')),1,1,'') 
             ) B

Select * from YourTable

Updated Table

Days    Frequency
1,2     {"weekly":"interval":1,"Sunday":true,"Monday":true,"Tuesday":false,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}
1,2,3   {"weekly":"interval":1,"Sunday":true,"Monday":true,"Tuesday":true,"Wednesday":false,"Thursday":false,"Friday":false,"Saturday":false}}

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Collectives™ on Stack Overflow

Updating database column with string built based on value of another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related