Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Select Casting Question

Posted on 2014-08-12
4
Medium Priority
?
205 Views
Last Modified: 2014-08-24
I have a file that I am trying to query with a column that contains formatted number values like

TotalRevenue
----------------
 99,200
500,000.00
4.00
1,200.00

I am trying to get all the rows that have total revenue > 100000.   When I run the query below I get an error "error converting nvarchar to int".   How can I fix the query below?

SELECT *
FROM [X.csv]
INNER JOIN [B.csv] ON [X.csv].[Product Code] = [B.csv].[Product_Code]
WHERE
([X.csv].[TotalRevenue] > 100000)
0
Comment
Question by:hojohappy
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40256132
First try:

WHERE
 ([X.csv].[TotalRevenue] > 100000.0)
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40256140
Seems that your TotalRevenue column is of type NVARCHAR(n). Thus there may be date which looks like a number, but cannot implicitly converted to INT. E.g.
DECLARE @Sample TABLE
    (
      TotalRevenue NVARCHAR(255)
    );

INSERT  INTO @Sample
        ( TotalRevenue )
VALUES  ( '  99,200' ),
        ( ' 500,000.00' ),
        ( ' 4.00' ),
        ( ' 1,200.00' ),
        ( ' 1.200.00' );

SELECT  *
FROM    @Sample S
WHERE   ISNUMERIC(S.TotalRevenue) = 0;

Open in new window

0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40256141
You need to change your WHERE clause to:
WHERE CAST(RTRIM(LTRIM([X.csv].[TotalRevenue])) AS decimal(9.2)) > 100000.00

Open in new window


There are two issues occurring. SQL is trying to cast the field into an integer because of the compared value, and your field appears to have leading and trailing spaces. You should also include the ISNUMERIC(<field>) = 1 in your WHERE clause:
WHERE CAST(RTRIM(LTRIM([X.csv].[TotalRevenue])) AS decimal(9.2)) > 100000.00
AND ISNUMERIC([X.csv].[TotalRevenue]) = 1

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40256385
>500,000.00
Part of the deal-i-o here is that numeric data types store only the number, and not a thousands-separating-comma, which makes the value not convertable to numeric.  You'll have to lose the comma to manipulate it as a number.

Something like (in addition to the above comments)
-- This throws an 'Error converting data type varchar to numeric' error. 
SELECT CAST('123,456.78' as numeric)

-- This works
SELECT CAST(REPLACE('123,456.78', ',', '') as numeric(8,2))

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

810 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