Conversion Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conversion Error

Author

Topic

pommguest99
Starting Member

16 Posts

Posted - 2008-08-28 : 13:27:50

Could someone save my balding head please as I cant stop pulling my hair.

This is a simple conversion where in, Im isolating all of special characters in the SSN column & once I strip out all of the special characters by using replace, Im trying to convert it into an INT.

I dont have lot of privileges in the environment & I cannot create any user defined functions(that I found in this forum). So I have to convert everything on the fly inside the query.

Here is what I have.

select convert(INT, ltrim(rtrim(stuff(AccountId,1,patindex('%[^0]%', AccountId)-1,'')))) as Sa,
convert(INT, ltrim(rtrim(stuff(Social,1,patindex('%[^0]%', Social)-1,'')))) as SCO
from
(select ltrim(rtrim(P.AccountId)) as AccountId, ltrim(rtrim(P.Socal)) as Social from
(select A.AccountId, case
when charindex(',',A.social) > 0 then replace(A.social, ',', '')
when charindex('.',A.social) > 0 then replace(A.social, '.', '')
when charindex('-',A.social) > 0 then replace(A.social, '-', '')
else A.social end as socal, A.first_name, A.last_name from
(select AccountId, social, first_name, last_name from [Cresto1] WHERE
AccountId not in ('999999999-9 ','999999999-6 ','999999999-1 ') and
social not in ('999999999-9 ','999999999-6 ','999999999-1 ') and
len(rtrim(ltrim(social))) ! 9 and isnumeric(social) = 1)A)P)Q

----------------------------------------------------------------

Here is the errror message that I see ----

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '8154678828' overflowed an int column. Maximum integer value exceeded.

Please help !!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-28 : 13:30:22

Convert it yo bigint

Jim