Replace first character in a string 1

[borisbe]

I have a character field with numbers (numbers in each record is different but the length of the numbers is the same in each record) and would like to replace the first number with a character. Example,

00255 change to x0255
01112 change to x1112
00044 change to x0044

Help would be appreciated.

Thanks

 

[KarveR]

select replace(your_field,substring(your_field,1,1),'x');

just by selecting, you should make sure it works ok, before changing this to an :

update table set your_field = replace(your_field,substring(your_field,1,1),'x';

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

KarveR, that'll replace every occurrence of the first character, not just the first one



borisbe, try this --

update yourtable
set thecolumn = concat('x',substring(thecolumn,2))

r937.com | rudy.ca

 

[KarveR]

thats how I understood the post.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

okay, but then this --

00044 change to x0044

would've said this --

00044 change to xxx44

but it didn't

r937.com | rudy.ca

 

[KarveR]

see what you mean doh!


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[borisbe]

Sorry that I wasn't able to post earlier. Thanks for the help. r937 statement worked great.