SQL Select Casting Question

hojohappy
hojohappy used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
First try:

WHERE
 ([X.csv].[TotalRevenue] > 100000.0)
Senior Developer
Commented:
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

Shaun KlineLead Software Engineer

Commented:
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

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial