James Hogle - 2 months ago 9

SQL Question

I have a table with a column called

`Days`

`Days`

`1,2`

`Sunday, Monday`

`Frequency`

`Days`

`1,2`

`Frequency`

`'{"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`

`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`

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