James Hogle James Hogle - 22 days ago 5
SQL Question

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

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?

Answer

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

Comments