CODEWITHSUNDEEP
sqlsql-serverstringt-sql
Taylor K.
Taylor K.

Reputation: 1085

Find first non-zero in a numeric string

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?

Upvotes: 5

Views: 5226

Answers (2)

John Woo
John Woo

Reputation: 263723

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

Upvotes: 12

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

If we talk about numeric data, it is sufficient to do cast(column as int) or as any other numeric data type.

Upvotes: 4

Related Questions