## 4 replies

Last post Apr 07, 2015 12:00 PM by bbcompent1

• bbcompent1

All-Star

35300 Points

9940 Posts

Moderator

### Changing numerical series start from 123% to 1400123%

Hey folks, I'm trying to figure out how I can update my table and take any varchar number that starts with 123 leaving the remaining numbers that follow alone and adding 1420 to the front of that.  So, say my original number is 123000001, I would like to turn that number into 1420123000001.  Can this change be done to a series of numbers like where ProdID between '123000001' and '123000159'?  Appreciate your guidance!  Thanks again!

Mark as answer posts that helped you.
• gimimex

Contributor

5450 Points

1123 Posts

### Re: Changing numerical series start from 123% to 1400123%

Hi,

Try something like this:

```update MyTable
set MyColumn = '1420' + ProdID
where ProdID between '123000001' and '123000159'```

Hope this helps.

• bbcompent1

All-Star

35300 Points

9940 Posts

Moderator

### Re: Changing numerical series start from 123% to 1400123%

Ok, now let us suppose I wanted to change the numerical sequence: 123000001 to 1420124000001? I knew I forgot to mention something...

Mark as answer posts that helped you.
• limno

All-Star

122238 Points

9703 Posts

Moderator

### Re: Changing numerical series start from 123% to 1400123%

``` CREATE TABLE test (ProdID varchar(50))
Insert into test values('123000001'),('123000159'),('888000159')

select Case when left(ProdID,3)='123' Then Replace(left(ProdID,3),  '123',  '1420124') Else left(ProdID,3) END
+substring(ProdID,4,len(ProdID))
from test
where ProdID between '123000001' and '123000159'

drop table test```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• bbcompent1

All-Star

35300 Points

9940 Posts

Moderator

### Re: Changing numerical series start from 123% to 1400123%

Ok, that works very nicely.  Now I will test this on a few records and let you know how it worked out.  Thanks!

Mark as answer posts that helped you.