Ltrim( )

[atiqraza]

hello everybody
I have a table with one colum in which i need to get rid of the leading 0's. It would have been easier to just change the datatype to int. However i can not since some columns have some characters in them.

LTRIM() only seems to get rid of leading blanks..Anybody know a function on how to get rid of Leading zeros.
i.e

00089 gets changed to 89.

Thanks

 

[gmmastros]

Take a look at this...

Declare @Temp Table(Data VarChar(20))

Insert into @Temp Values('0000089')
Insert into @Temp Values('24')
Insert into @Temp Values('Eight')
Insert into @Temp Values('00000A')

Select 'Before', Data From @Temp

Update @Temp
Set    Data = Case When IsNumeric(Data + 'e0') = 1
                   Then Convert(VarChar(20), Convert(Int, Data))
                   Else Data
                   End


Select 'After', Data From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

UPDATE Table SET column = CONVERT(INT,column)
WHERE Column NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1






Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[atiqraza]

Thnx guys... damn you guys are good

 

[yan19454]

What is IsNumeric(Data + 'e0') = 1

'eo' here imply ?

Thx.

 

[gmmastros]

yan,

e0 is explained in this Faq: faq183-6423



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom