SQL Select Casting Question

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)
hojohappyAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
First try:

WHERE
 ([X.csv].[TotalRevenue] > 100000.0)
0
 
Shaun KlineLead Software EngineerCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.