SQL Select Casting Question

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


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?

FROM [X.csv]
INNER JOIN [B.csv] ON [X.csv].[Product Code] = [B.csv].[Product_Code]
([X.csv].[TotalRevenue] > 100000)
Question by:hojohappy
    LVL 68

    Expert Comment

    First try:

     ([X.csv].[TotalRevenue] > 100000.0)
    LVL 32

    Accepted Solution

    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.
          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

    LVL 25

    Expert Comment

    by:Shaun Kline
    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

    LVL 65

    Expert Comment

    by:Jim Horn
    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


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now