Solved

SQL: Convert Varchar to Int

Posted on 2008-10-31
11
1,648 Views
Last Modified: 2010-04-21
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
Comment
Question by:JDCam
  • 5
  • 3
  • 3
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
 
LVL 22

Expert Comment

by:dportas
Comment Utility
CAST(REPLACE(field2,',','') as INT)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
DOH!

Sometimes the solution is so simple.
0
 

Author Comment

by:JDCam
Comment Utility
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
 
LVL 22

Expert Comment

by:dportas
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:JDCam
Comment Utility
Hmm.. every row was returned.
all appear to have the same format as  
1,096.88

0
 
LVL 22

Accepted Solution

by:
dportas earned 250 total points
Comment Utility
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
 

Author Comment

by:JDCam
Comment Utility
Ok, Now no records are returned.
0
 

Author Comment

by:JDCam
Comment Utility
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:JDCam
Comment Utility
Thanks Guys,
I split the points between you on this one
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now