bitbucket-25253 (3/11/2013)
CREATE TABLE T(COMCOD nchar(4), ACTCODE nvarchar(6), ACTDESC nvarchar(250),PRIMARY KEY (ACTCODE))
INSERT INTO T
SELECT '3306', '180001','ADVANCE TO STAFF' UNION ALL
SELECT '3306', '180002','ADVANCE TO OTHERS' UNION ALL
SELECT '3306', '180003','ADVANCE TO SITE OFFICE'
UPDATE T SET ACTCODE = '190' + SUBSTRING(ACTCODE,4,3)
WHERE SUBSTRING(ACTCODE,1,3) = '180'
Results:
COMCODACTCODEACTDESC
3306 190001 ADVANCE TO STAFF
3306 190002ADVANCE TO OTHERS
3306 190003ADVANCE TO SITE OFFICE
Wouldn't STUFF be a better choice? You know you're replacing the first 3 characters, you don't know that the length of ACTCODE is always the same.
UPDATE T
SET ACTCODE = STUFF(ACTCODE, 1, 3, '190')
WHERE ACTCODE LIKE '180%';