scott_lotus scott_lotus - 4 months ago 11
SQL Question

T-sql script to replace first 2 numbers in a string

Table

Parameter
contains column
SerialNumber
. The value
SerialNumber
is prefixed with the number 99. Sample:
99064960
.

Table.Parameter
-------------
[ParameterID] [int] IDENTITY(1,1) NOT NULL,
[SerialNumber] [varchar](10) NULL,


I would like to script a change to replace 99 with 88 on all rows.

Expected result:
88064960
.

Using the following statement the issue was resolved but question was posted to amalgamate these functions into a single statement.

UPDATE table
SET SerialNumber = SUBSTRING(SerialNumber, 3, LEN(SerialNumber)-2)
WHERE ID IN ( )


This string then added the prefix:

update table
set SerialNumber = '88' + SerialNumber
where ID in ( )


Thank you.

rcs rcs
Answer

How about this:

UPDATE mytable
SET field = '88' + SUBSTRING(field, 3)
WHERE SUBSTRING(field, 1, 2) = '99'