Last post Mar 25, 2013 12:54 AM by kedarrkulkarni
Mar 25, 2013 12:14 AM|kengkit|LINK
I have plenty of records in my table.
How can i add 00 in front of the digi? such as..
Mar 25, 2013 12:42 AM|aaa_78600|LINK
Mar 25, 2013 12:54 AM|kedarrkulkarni|LINK
if the possition of numeric value is fixed... i.e. first character is always X and u have to put 00 as second and third character then, u can simpy write
update TableName set columnName = 'X' + substring(columnName,2,len(columnName)-1)
but, if the position of number is not fixed, i.e. if starting character could be like XY452126 then, use below query
SELECT SUBSTRING(columnName,0,patindex('%[0-9]%', columnName)) + '00' + SUBSTRING(columnName,patindex('%[0-9]%', columnName),LEN(columnName))
hope this helps...