Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

Question

Using a script to transform column values in a Table

Jun 10, 2012 10:16PM PDT

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

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?

Discussion is locked

- Collapse -
Answer
What DBMS are you using?
Jun 10, 2012 10:42PM PDT

You'll need to use the SQL dialect it supports.

This (but I didn't check the syntax) is how I would do it in MS Access:
iif(left(ac,1)="R";"100"&right(ac,5);iif(left(ac,1)="M";"200"&right(ac,5);iif(left(ac,1)="T";"300"&right(ac,5)))

It isn't exactly SQL, I think, but it's an expression MS Access understands. But the use of substrings and string concatenation should be supported by your DBMS also.

By the way, as a programmer you should know the difference between a digit and a number, so you can use the right term in the specification, the documentation and the test report.

Kees

Kees

- Collapse -
SQL Server Management Studio 2010
Jun 10, 2012 10:54PM PDT

I guess I can import my data into Access if its that easy. I'm very new to programming so sorry about that one. I'll give it a try in access.

- Collapse -
Re: import
Jun 10, 2012 11:25PM PDT

No need to import in (and export from) MS Access. You can link to the table in MS SQL Server (not in the Management Studio!) via ODBC.

But why not start learning the SQL variant of MS SQL Server? Much more useful. Have your boss pay for some books and a 3-day introduction course. A good investment for a beginning programmer.

If it were me, I would never let a beginning programmer touch my production data with a script written in a language he doesn't know. Your boss surely must trust you. So I assume he'll is willing to invest in you also.

Kees

- Collapse -
SQL Server Management Studio ...
Jun 11, 2012 4:14AM PDT

is just a tool to manage an SQL Server, as the name so clearly states. It's not a DBMS.

Kees

- Collapse -
How would I do this in Access?
Jun 10, 2012 11:37PM PDT

I just imported the data into access. How would I go about doing this?

- Collapse -
(NT) With a query.
Jun 11, 2012 4:12AM PDT