Get rid of leading 0s of a varchar field

Hi,
I have a field called StreetNo in a table called Prospects, the field is a
varchar(10). The table is populated by someone else, so I have no control
over what kind of data entered into the table. The StreetNo I got is a
complete mess, e.g. 00001, 01234, 01234a, 0000 PO Box. Is there a SQL
statement that I can use to show the StreetNo without the leading 0s?
I tried:
select Case when StreetNo is null then '' else convert(varchar(10),
convert(integer, StreetNo)) + ' ' end
This is not working for 01234a or 0000 PO Box
TIA
TIABetter to use a 3rd party product or a programming language for data
cleanup. With t-SQL, given all the data values are messed up, you can try
something like:
SELECT STUFF( @.s, 1, PATINDEX( '%[^0]%', @.s ) - 1, SPACE(0) )
Anith