CODEWITHSUNDEEP
sqlsql-servercastingnumericvarchar
Craig
Craig

Reputation: 33

Converting a VARCHAR to NUMERIC - No Previous Post Answer Works For Me

I've read many responses to this same question, but none of the answers are working for my latest attempt at CASTing a VARCHAR to NUMERIC:

Cast(Cast(a.VARCHARFIELD as NUMERIC(20,0))as INT)

The error I get is:

The conversion of the varchar value '97264634555 ' overflowed an int column. Maximum integer value exceeded.

Unfortunately, the a.VARCHARFIELD contains accounts like 12345678999 but it also contains text or VARCHAR values like:

BALL                           
TWIN                          
12345678999                       
12345679000

Upvotes: 0

Views: 1367

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

First, you need to determine whether your value is a number. There is no ANSI standard method, but an approximation is to just see if it starts with a number.

Second, int is too small, so I would recommend a decimal format.

So something like this would work on the data you provided:

select (case when VARCHARFIELD between '0' and '99999999999999'
             then cast(VARCHARFIELD as decimal(20, 0))
        end)

The validation of number can be done much better in any particular database; the form given is sufficient for the data provided in the question.

EDIT:

In SQL Server, a more accurate method would be:

select (case when VARCHARFIELD NOT LIKE '%[^0-9]%'
             then cast(VARCHARFIELD as decimal(20, 0))
        end)

Upvotes: 2

Related Questions