SQL function to map key to value

May 6, 2022

I am trying to create a SQL function to map keys to values. The goal is to migrate a table that contains Windows timezone names to iana timezone name, something like this

Insert Into IanaTable (IanaTimezone)
Select TzConvert(WindowsTimeZone)
From WindowsTzTable

I see that there is around 50 windows timezones and willing to write manual conversion for those 50 items. I have looked at some other answers but I hope not to write a very big IF/ELSE function

SQL function to manipulate values

>Solution :

IF is used for flow control. For expressions, just use CASE:

Insert Into IanaTable (IanaTimezone)
    Select 
        case
            when CONDITION1 then RESULT1
            when CONDITION2 then RESULT2
            ......
            when CONDITION100 the RESULT100
        end as IanaTimezone         
    From WindowsTzTable

Edit: Since OP stated that they would prefer a function because the same value will be assigned to multiple columns, I counterpropose a lateral join to alias the expression. This is better since even copy-pasting the name of a function is still WET and prone to errors. Here:

Insert Into IanaTable (...)
    Select 
         q1.IanaTimezone as column1
        ,q1.IanaTimezone as column2
        ,q1.IanaTimezone as column3
        ,othervalue      as othercolumn
        ......
    From 
        WindowsTzTable
        cross apply
        (
            Select 
            case
                when CONDITION1 then RESULT1
                when CONDITION2 then RESULT2
                ......
                when CONDITION100 the RESULT100
            end as IanaTimezone 
        ) as q1