Removing leading zeros from a string in SQL Server 2008 R2

Question

I have a string representing a batch number of goods, for example "0000AZA001B". I only need the "AZA001B" part. How can I achieve that in SQL Compact?

Answer 1

Use the following syntax to trim an unknown number of leading zeros:

SELECT REPLACE(LTRIM(REPLACE(BatchNumber, '0', ' ')), ' ', '0')

This replaces all zeros in your string with spaces, uses the LTRIM function to trim leading spaces and converts the spaces back into zeros.

Answer 2

There is so many way to do this..check this example and links.

declare @str varchar(50) = '0000AZA001B'

select 
    @str , 
    substring(@str, patindex('%[^0]%',@str), 10),
    REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0')

Removing leading zeroes from a field in a SQL statement

http://sqlmag.com/t-sql/trimming-leading-zeros

Updated as SQL Compact version Try this :

select CONVERT(BIGINT, REPLACE(LTRIM(REPLACE('000000000011070876', '0', ' ')), ' ', '0')) 

Convert from string with leading zeros to bigint in SQL Server CE isn't working

Still you want further analyze, you can download this tool :- https://sqlcequery.codeplex.com/