Find first non-zero in a numeric string

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 is to use string manipulation, such as CHARINDEX. However, I would need to search from left-to-right for the first occurrence of NOT 0. How can I accomplish this in SQL Server?

Answer 1

By the way, why did you store tha 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 talk about numeric data, it is sufficient to do cast(column as int) or as any other numeric data type.