Remove leading zero's on an INT datatype

[rzirpolo]

I initially did a search on the exisiting forums and there is a few topics on this but they all relate to strings (varchar, char, etc.) I have an integer column where I would like to remove the leading zero (if there is one).

I can find out what rows do have this but am getting puzzled in regards to what is the best way to do it as the RTRIM function works only for leading spaces ?

I just want some fresh ideas that maybe I haven't thought of ?

 

[bunmiA]

1) change these fields to 'char' fields using 'convert'
2)find what rows have them... you have been able to achieve this.
3) use REPLACE
REPLACE(<fieldname>,'0','')
4) change field back to int using 'convert'

 

[rzirpolo]

REPLACE would not be a good function to use would it ? If there are any other zero's present in the string it will also replace them won't it ?

 

[bunmiA]

good point... but you could replace the first letter doing this

REPLACE(<fieldname>,left(<fieldname>,1),'')

havent tested it... but it should work

 

[rzirpolo]

Nice, thanks I didn't think of that. I have just thought of something though....

Is what I have typed a false statement ? What I mean is that I don't think a column that is a datatype of integer will allow a leading zero ? I'm under the assumption that it will automatically cut any leading zero's off ?

I came across this when updating table info to create some test data.

 

[SQLBill]

Are you sure there are leading zeros? INT datatype doesn't allow leading zeros. Try this to test it:

DECLARE @myint INT
SET @myint = 01
SELECT @myint

it will return 1 not 01.

Are you sure the column is of INT datatype?

-SQLBill