Strip Trailing Zeros 1

[lyudmila]

How can strip trailing zeros from one of the field in my table, data type decimal(10,0), but string has different length.
Example of data values:
14080
140800
1408000
256479900
25647990
2564799000
230880
2308800
23088000

I need to use this field as a link, but data is not consistent. People who key in data, have no rules and limitations. I need to get rid off those zeros.

Any idea how I can accomplish this task?

 

[cheyney]

here is one way:

replace all 0's with spaces, then to a right trim, then replace all spaces with zeros

like this:

REPLACE(RTRIM(REPLACE(CAST(mycolumn AS VARCHAR), '0', ' ')) , ' ', '0')

Cheyney

 

[Zathras]

cheyney: What about 1408 and 23088?

 

[JamesLean]

As he only wanted to strip trailing zeroes, cheyney's method will work fine - I thought it was quite neat actually

 

[Zathras]

Ah! Right! I see it now. Quite neat indeed. A casual glance made me think that it would have killed inside zeroes as well.

 

[lyudmila]

Thank you, cheyney!
It works, replaces all zeros (in middle of the string too)with ' ', then trims trailing zeros, then puts back all zeros in the middle of the string.
From input 20379800 I get 203798, exactly what I need.
Thanks again!