Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1800
  • Last Modified:

SQL: Convert Varchar to Int

I am trying to use CAST to convert a Varchar to an Integer

Update Table1
set field1 = (select CAST(field2 as INT) from table2 where......

The values in field 2 are all numeric with 2 decimals. The error reads: " Syntax error converting the varchar value of '1,456.88' to a column of data type int.

based on other posts, I am assuming it is the comma it dosent like. But I am guessing, and not sure how to go about removing it in the process.

Thanks,

0
JDCam
Asked:
JDCam
  • 5
  • 3
  • 3
2 Solutions
 
BrandonGalderisiCommented:
Try:


select cast(floor(cast('1,456.88' as money))as int)

Without the floor, it will round to the nearest.

1456.49 = 1456
1456.50 = 1557


so

Update t1
set field1 = cast(floor(cast(t2.field2 as money))as int)
from table1 t1
join Table2 t2
on Table1.WHAT  = table2.WHAT
where
0
 
dportasCommented:
CAST(REPLACE(field2,',','') as INT)
0
 
BrandonGalderisiCommented:
DOH!

Sometimes the solution is so simple.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JDCamAuthor Commented:
I typed exactly what you have, even cut and paste to be sure (changed the field name of course) but the same error persists

 (SELECT     CAST(REPLACE(User5, ',', '') AS INT) AS Expr1
                            FROM          SKUInfo
                            WHERE    

when i run it, SQL Mgt Studio adjusts some spacing etc, and adds 'AS Expr1' , but the error is the same
0
 
dportasCommented:
Then I guess you have some other non-numerics in the table that are causing a problem.

Try the following query to find them:

SELECT User5
FROM SKUInfo
WHERE User5 LIKE '%[^0-9.]%';
0
 
JDCamAuthor Commented:
Hmm.. every row was returned.
all appear to have the same format as  
1,096.88

0
 
dportasCommented:
I forgot to include , in the LIKE expression:

SELECT User5
FROM SKUInfo
WHERE User5 LIKE '%[^0-9.,]%';

The point of this is to return any rows that contain non-numeric characters.
0
 
JDCamAuthor Commented:
Ok, Now no records are returned.
0
 
JDCamAuthor Commented:
Although the error message gripes about converting to INT, I just noticed in the database, the destination field is type 'Dimension:float'
Does this change anything??
0
 
BrandonGalderisiCommented:
It changes that you don't want to cast to an int anymore, but rather a float.

using dportas' simpler code, the whole thing would look like...
Update t1
set field1 = cast(replace(t2.field2,',','') as float)
from table1 t1
join Table2 t2
on Table1.WHAT  = table2.WHAT
where....

Open in new window

0
 
JDCamAuthor Commented:
Thanks Guys,
I split the points between you on this one
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now