Find the first nonzero number in a number line

Question

I have a char(12) column with data such as:

 000000004012 000000615737 000000000012 000000000100 

And I need to convert it to this:

 4012 615737 12 100 

My initial thought was to use string manipulations like CHARINDEX. However, I will need to search from left to right for the first occurrence of NOT 0 . How to do this in SQL Server?

Answer 1

By the way, why did you store data in char ? but to answer your question try this,

 SELECT CAST(colName AS INT) 

or

 SELECT CAST('999999999999' AS NUMERIC(12,0)) 

SQLFiddle Demo

Answer 2

If we are talking about numeric data, just cast(column as int) or just like any other type of numeric data.