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?

Answers


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

Need Your Help

Kibana Installation - Blank Dashboard

kibana-4

Kibana is accessible from http://127.0.0.1:5601 but shows a blank page except for top banner. I have tried the installation local to my machine and it works. Maybe, I am not opening certain ports?

Image Thumbnails ASP.NET

asp.net image-processing thumbnails

I'm trying to genererate thumbnails from image files stored in a folder under the website root, I am currently using the built in capebilites in .NET to dynamically generate the thumbnails but the