Transform Column values in Table

[Mnelson]

Hello, 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

R= 100 M=200 T= 300

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 have thousands of this account numbers that I need to change. How would I do this in Access?

[June7]

Do you actually want to change the data in the table or just for display purposes?

How many letters are involved?

What code lanquage does SQL Management Studio use?

You can do this conversion by constructing a field in query with expression, like:

Switch(Left(fieldname,1)="R",100, Left(fieldname,1)="M",200, Left(fieldname,1)="T",300,) & Mid(fieldname,2)