Replace letters by 0 (zeros)

[Alfredomulet]

Hello there, thanks for the previous responses. Is it there an easy way to replace one character of a field by 0 (zero) using just SQL?

For example:
TXN_AMT
000024584862R
000000473929J
000000012929A
000005013141S

to
TXN_AMT
0000245848620
0000004739290
0000000129290
0000050131410

The length of the field is allways 13, and the letter to replace is allways the last character on the right.
cheers,
Alfredo

 

[r937]

to retrieve only,

select left(yourfield,12)&'0' 
  from yourtable

to make it permanent,

update yourtable
   set yourfield = left(yourfield,12)&'0'

you did not say so, but if the 13th position can sometimes contain numerics, then obvioulsy you'd want to IIF the above statements

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[grande]

I believe it'd be something like

Replace(Right([yourfield], 1), "0")

-------------------------
Just call me Captain Awesome.

 

[grande]

I believe it'd be something like

Replace(Right([yourfield], 1), "0")

-------------------------
Just call me Captain Awesome.

 

[Alfredomulet]

R937,

Thanks a lot for your response. It includes sometimes numbers, indeed.

cheers,

Alfredo

 

[r937]

select iif(isnumeric(mid(yourfield,13,1))
       , yourfield,left(yourfield,12)&'0')  
  from yourtable

update yourtable
   set yourfield 
     = left(yourfield,12)&'0'
 where not isnumeric(mid(yourfield,13,1))

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Alfredomulet]

Thanks a lot! it worked like a charm!