Query to edit an entire column of values at once

[samdahlr]

Hello,



I have a column of data that are contact ID's. They are in this format "ESP-CON-123456". I want to get rid of the "ESP-CON-" portion for the entire column and have each ID in the format of "123456". Anyone have a query for this preferably in SQL?

Thank you!

[JoeM]

Are the IDs always exactly 14 characters long and you want to get rid of exactly the first 8 characters?

[samdahlr]

No some of them are 12 characters, and just a few of them do not have that exact format, but almost all of them do.

[JoeM]

We need to know of all the possible formulations so we know how to write the formula to do what you want.
Can you list the other possible layouts?
For example, the IDs that are 12 characters, how many characters do you want to lose/keep on those?

Basically, we need to come up with a complete set of rules to handle each possible scenario in order to program something to do what you want.

[samdahlr]

I found out the other formats were a typo in the database, so I will delete those separately. Here are the only two formats:

ESPE-CON-123456

ESPE-CON-12345

(I forgot the last E on ESPE in my original post. The first 9 characters are always "ESPE-CON-", and I only want the remaining characters.)

Thank you

[JoeM]

In Access, the SQL code would look something like this:

UPDATE TableName
SET TableName.ContractID = Mid(TableName.ContractID,10,6);

In native SQL, you would substitute the MID function with SUBSTRING (that is SQL's equivalent of the Access MID function).