Need Help using Replace in an Update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help using Replace in an Update statement

Author

Topic

rwsjbs
Starting Member

17 Posts

Posted - 2008-02-10 : 15:47:05

We store an item’s barcode number in an SQL table named ProductNumber. The field we store the number in is called Number. Currently we are using all 12 digits on the barcode. We are switching to only use the 10 digits in the middle of the barcode. So, I’m trying to:

1. Update all numbers in field ProductNumber.Number that are 12 digits
2. Then trim off the 1st digit and the last (12th) digit
3. So, this leaves me with the 10 middle digits

Here is an example:

Currently the number is:
710817117329

I need it be updated to
1081711732

I would appreciate if someone could show me the correct SQL statement to select the numbers that equal 12 and delete the 1st and 12th digit leaving me with 10 digits.

Thank you!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-10 : 15:54:37

Update ProductNumber
Set Number = substring (Number,2,10)
Where len(RTRIM(LTRIM(Number)) = 12

You don't need replace, you need substring.

Poor planning on your part does not constitute an emergency on my part.