Using a script to transform column values in a Table
I'm new to scripting so stick with me. I'm trying to create a script that will transform the column values in my Table. I'm currently using SQL Management Studio.
Here's what I'm trying to do:
I have a column with account numbers in this form:
R12345 M23456 T12345
I'm trying to convert the first letter to a sequence of numbers then keep the remaining numbers. For example:
R12345 to 10012345 M23456 to 20023456 T12345 to 30012345
I cant seem to find the file to run this script off of sad The reason I'm doing this is to create a 1 to 1 relation with this table and another table that is in the form of just those numbers.I've been stuck on this problem for some time now I have way too many records in this table to do it myself. Any thoughts on how I should go about doing this? Any particular DBMS or scripting language that I should be using?
UPDATE accounts_table SET account_number=CONCAT( CASE WHEN SUBSTR(account_number,1,1)='R' THEN '100' WHEN SUBSTR(account_number,1,1)='M' THEN '200' WHEN SUBSTR(account_number,1,1)='T' THEN '300' END, SUBSTR(account_number,2) )