Updating Character String with sql

[Gmuchan]

Hi,


Help needed please.

Basically I am wanting to update a 16 digit character string which is in a table called say accounts.

If the account no. field is say 0502562389568745 I want to amend every record by taking away the leading zero of the account no. and replace it with a 9.

So the result would be 9502562389568745 and I want to run this so that it will update every account no. in the accounts table.

I know its an update statement but wasn't sure how to pull the first character in the string out and whether I used the replace statement as well?!

I am new to sql and have been trawling the net and books for answers but come up with nothing as yet so any help would be amazing!!!

Thanks

Graeme

[iburyak]

Try this:

 
update accounts
set [account no] = '9' + substring([account no],2,100)
where substring([account no],1,1) = '0'

Good Luck

[ck9663]

Try this:

 
update accounts
set [account no] = '9' + substring([account no],2,100)
where substring([account no],1,1) = '0'

Good Luck

note:
this will only replace if the first digit is '0' and will only replace the first digit. if you're expecting 2 or more '0'-prefix, you're going to do some coding for that one...

[Gmuchan]

Thanks for the help guys.


That was hugely useful!!!!


G