How to trim leading zeros from a varchar column?

My table has a column named [Liability] varchar datatype which has the data in the format

(

3535.00,

00393.99,

00Loan,

0.00,

.00

*.00

)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)

(

3535.00,

393.99,

Loan,

0.00,

.00

*.00

)

Can someone show my the sql statement for this?

Thanks.

Code Snippet

--To ignore leading '0' and <SPACE> characters:

select substring(Liability,patindex('%[^0 ]%',Liability),8000)

from <MyTable>

--To ignore ONLY leading '0' characters:

select substring(Liability,patindex('%[^0]%',Liability),8000)

from <MyTable>

|||

rusag2,

I think that your suggested solution will also remove the leading zero from [ 0.00 ] which 'should' be retained.

Perhaps something like this would be closer to the desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( '3535.00' )
INSERT INTO @.MyTable VALUES ( '00393.99' )
INSERT INTO @.MyTable VALUES ( '00Loan' )
INSERT INTO @.MyTable VALUES ( '0.00' )
INSERT INTO @.MyTable VALUES ( '00.00' )
INSERT INTO @.MyTable VALUES ( '.00' )
INSERT INTO @.MyTable VALUES ( '*.00' )

SELECT

CASE
WHEN isnumeric( MyValue ) = 1 THEN cast( cast( MyValue AS decimal(18,2)) AS varchar(20))
ELSE substring( MyValue, patindex('%[^0]%', MyValue ), 20 )
END
FROM @.MyTable

--

3535.00
393.99
Loan
0.00
0.00
0.00
*.00

|||

Thanks Arnie.

That worked!!!!!

sql