?
Solved

SQL: Convert Varchar to Int

Posted on 2008-10-31
11
Medium Priority
?
1,721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22851616
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
ID: 22851813
CAST(REPLACE(field2,',','') as INT)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22851859
DOH!

Sometimes the solution is so simple.
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 1

Author Comment

by:JDCam
ID: 22851915
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
ID: 22852049
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
 
LVL 1

Author Comment

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

0
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 22852136
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
 
LVL 1

Author Comment

by:JDCam
ID: 22852156
Ok, Now no records are returned.
0
 
LVL 1

Author Comment

by:JDCam
ID: 22852268
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 1000 total points
ID: 22852312
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
 
LVL 1

Author Closing Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 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