(SQL) Writing a stored procedure to replace months with numbers across multiple columns (ex jan-2 to 1/2)

Question

I have a file that has multiple address columns (10+) where numbers have been converted to dates. For example, "Apartment 5/6" has been converted to "Apartment May-6." I'm trying to write a stored procedure with dynamic sql to update all these columns at once. However, the only way I can think to write it would be extremely long and probably inefficient. I've posted a snipit of my current format below. I would need to do this for all 12 months and 10+ columns.

ALTER PROC [Addressfix] @date [VARCHAR](10) 
AS 
  BEGIN 
      DECLARE @SQL NVARCHAR(max); 

      BEGIN 
          SET @SQL =' update AddressTable_' + @date 
                    + ' set address= case when [address] like ''%1-jan%'' then replace([address],''1-jan'',''1/1'')  when [address] like ''%2-jan%'' then replace([address],''2-jan'',''1/2'')  when [address] like ''%3-jan%'' then replace([address],''3-jan'',''1/3'')  else [address] end from AddressTable_' + @date + ' where address like ''%Jan%'' ' 

          EXEC (@SQL) 
      END 

      BEGIN 
          SET @SQL=' update AddressTable_' + @date 
                   + ' set address= case when [address] like ''%1-feb%'' then replace([address],''1-feb'',''2/1'')  when [address] like ''%2-feb%'' then replace([address],''2-feb'',''2/2'')  when [address] like ''%3-feb%'' then replace([address],''3-feb'',''2/3'') else [address] end from AddressTable_' + @date + ' where [address] like ''%feb%'' ' 

          EXEC (@SQL) 
      END 

      BEGIN 
          SET @SQL = 'update AddressTable_' + @date 
                     + '  set address1=  case when [address1] like ''%1-jan%'' then replace([address1],''1-jan'',''1/1'')  when [address1] like ''%2-jan%'' then replace([address1],''2-jan'',''1/2'')  when [address1] like ''%3-jan%'' then replace([address1],''3-jan'',''1/3'')  else [address1] end from AddressTable_' + @date + ' where [address1] like ''%Jan%'' ' 

          EXEC (@SQL) 
      END 

      BEGIN 
          SET @SQL=' update AddressTable_' + @date 
                   + ' set address1= case when [address1] like ''%1-feb%'' then replace([address1],''1-feb'',''2/1'')  when [address1] like ''%2-feb%'' then replace([address1],''2-feb'',''2/2'')  when [address1] like ''%3-feb%'' then replace([address1],''3-feb'',''2/3'') else [address1] end from AddressTable_' + @date + ' where [address1] like ''%feb%'' ' 

          EXEC (@SQL) 
      END 
  END 

Is there a more efficient way to do this? Perhaps using a cursor? Any help would be greatly appreciated!

Answer 1

Welcome to Stack Overflow. You're question is "Is there a more efficient way to do this?".

I actually don't know the answer. However, I wondered if it could be done using a single query. In Microsoft T-SQL, it can. Here's how it could be done:

UPDATE
        AddressTable_2020_09_14
   SET
        ADDRESS = STUFF( ADDRESS
                        ,PATINDEX( '%[1-9]-[jan|feb|mar]%', ADDRESS)
                        ,5
                        ,CASE SUBSTRING( ADDRESS
                                        ,PATINDEX( '%[1-9]-[jan|feb|mar]%', ADDRESS) + 2
                                        ,3)
                             WHEN 'jan' THEN '1'
                             WHEN 'feb' THEN '2'
                             WHEN 'mar' THEN '3'
                         END
                            + '/'
                            + SUBSTRING( ADDRESS
                                        ,PATINDEX( '%[1-9]-[jan|feb|mar]%', ADDRESS )
                                        ,1 )
                       )
 WHERE
        PATINDEX( '%[1-9]-[jan|feb|mar]%', ADDRESS ) > 0

Caveat: I have not tested to see if this is more efficient. Nor have we defined what more efficient means in your context. I leave that up to you.

Caveat: Translating this to your version of SQL may be impossible.

Exercise: I leave making it dynamic, extending it to 12 months, and adding the additional columns up to you. Note that this will not work if your apartment numbers before the slash are greater than nine.

Final Disclaimer: Your question puts the month first (May-6), but your code puts the day first (1-Jan). I wasn't sure which to code for, so I coded to match your code and not your question.

Good Luck!