Remove part number prefix and suffix to determine the root number and save... 2

[Webkins]

I am usnig SQL 2000. I have a SQL table with column name MATERIAL, datatype VARCHAR.
In this column is data which has this format: A00671001

What I would like to do is to remove the first 2 positions, "A0" and the last 3 positions "001" to end up with the root part number "0671".
I would like to then save this new number in the original column MATERIAL, replacing the original part number.

I have no code to post as I do not know where to start.
Any suggestions or ideas will be greatly appreciated. Thank you.

 

[yelworcm]

I am usnig SQL 2000

I am so sorry.

If the lengths of the data are pretty constant

substring (material, 3, 4)

If not, it gets a bit lengthy. Basically, you can use a combination of substring, and reverse to get what you need. I would need some time to work out the exact code, though.

 

[Webkins]

Ahhhh, I was going to mention that but I forgot, sorry. All the varchar part numbers are 9 characters long.

 

[simian336]

As yelworcm suggested you can do

select Material, substring(material, 3, 4)
from yourtable

to test with and compair.

Then, if that works you would say

update yourtable
set material = substring(material, 3, 4)

Simi