CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
James Hogle
James Hogle

Reputation: 3040

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?

Upvotes: 2

Views: 31

Answers (2)

James Casey
James Casey

Reputation: 2507

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

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81960

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',' ')

Upvotes: 1

Related Questions