Converting VARCHAR to BIGINT

  • I am working on a project where I need to move values from one table (tableOriginal) into another table (tableNew). The original table has two columns EstCards / EstAccts that are stored as varchar but in the new table, we want these values stored as integer (technically bigint because some values in the original table for some reason are in the billions).

    When I do the following SQL Statement (see pasted below), I am getting an error saying (this will be added to the SELECT INTO statement but for now just need to convert the varchar values to bigint and if they cannot be converted, enter zero in the new table):

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    I am not sure what record this is breaking on to know what I am missing. Do you know of a way to check if a varchar can be converted to a number and if it cannot, I want to insert a zero or even know how to find the record that is failing the test? I really only think I need only one “WHEN” statement, but I am trying anything I can think of.

    SELECT

    PID

    , IDate

    , BegDate

    , EndDate

    , CASE

    WHEN EstCards IS NULL THEN 0

    WHEN LEN(EstCards) = 0 THEN 0

    WHEN ISNUMERIC(EstCards) = 0 THEN 0

    WHEN ISNUMERIC(EstCards) = 1 THEN CAST(EstCards AS BIGINT)

    ELSE 0

    END

    , CASE

    WHEN EstAccounts IS NULL THEN 0

    WHEN LEN(EstAccounts) = 0 THEN 0

    WHEN ISNUMERIC(EstAccounts) = 0 THEN 0

    WHEN ISNUMERIC(EstAccounts) = 1 THEN CAST(EstAccounts AS BIGINT)

    ELSE 0

    END

    , 0

    , 0

    , 'Data Migration'

    , 'svc'

    , GETDATE()

    FROM

    tableOriginal

    Any help is appreciated

  • Have a look at the script here

    http://www.sqlservercentral.com/scripts/IsNumber/70211/

  • Appreciate the help, but I do not have access to create database objects. We "own the data, but not the objects / database", so if possible, I need to be able to do this with built in T-SQL.

  • No need for a performance problem caused by a scalar udf. There's a simple formula explained in the following article that can help you simplify your code.

    http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

    SELECT

    PID

    , IDate

    , BegDate

    , EndDate

    , CASE

    WHEN EstCards NOT LIKE '%[^0-9]%' THEN CAST(EstCards AS BIGINT)

    ELSE 0

    END

    , CASE

    WHEN EstAccounts NOT LIKE '%[^0-9]%' THEN CAST(EstAccounts AS BIGINT)

    ELSE 0

    END

    , 0

    , 0

    , 'Data Migration'

    , 'svc'

    , GETDATE()

    FROM

    tableOriginal;

    Note: This won't handle negative values. You'll need to modify the condition to handle negative values correctly.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You should also be able to use ISNUMERIC to see what data is not numeric and therefore can't be converted.

    -SQLBill

  • If by any chance you're working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.

    That wasn't my first suggestion because this is the 2008 forum, so it might not apply to you.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (2/25/2016)


    If by any chance you're working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.

    Have you compared it for performance?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (2/25/2016)


    Luis Cazares (2/25/2016)


    If by any chance you're working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.

    Have you compared it for performance?

    I hadn't test it for performance myself, but I just did. The use of TRY_CAST or TRY_CONVERT uses minimal resources and is almost as fast as not converting the data at all. Using the CASE statement is about 5 times slower than using just functions.

    Here's the quick test that I made. I changed the amount of invalid values but didn't impact the performance.

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    E2(n) AS(

    SELECT a.n FROM E a, E b

    ),

    E4(n) AS(

    SELECT a.n FROM E2 a, E2 b

    ),

    cteTally(n) AS(

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E4 a, E2 b

    )

    SELECT CAST( n AS varchar(7))

    + CASE WHEN n < 100000 THEN 'a' ELSE '' END --adding some invalid conversions

    AS nString

    INTO #Test

    FROM cteTally;

    DECLARE @nString varchar(7),

    @nBigInt bigint;

    SET STATISTICS TIME ON;

    PRINT 'Dry run';

    SELECT @nString = nString

    FROM #Test;

    PRINT 'Using TRY_CONVERT';

    SELECT @nBigInt = ISNULL( TRY_CONVERT( bigint, nString), 0)

    FROM #Test;

    PRINT 'Using CASE';

    SELECT @nBigInt = CASE WHEN nString NOT LIKE '%[^0-9]%' THEN CAST(nString AS BIGINT)

    ELSE 0

    END

    FROM #Test;

    SET STATISTICS TIME OFF;

    GO

    DROP TABLE #Test;

    I remember that you mentioned a similar issue when formatting dates using CASE instead of native functions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • dogramone (2/25/2016)


    Have a look at the script here

    http://www.sqlservercentral.com/scripts/IsNumber/70211/

    That's a Scalar Function with more than 1 While Loop. While it may do the job, it's RBAR on steroids and I would strongly recommend avoiding it. Please see the following article for 1 high performance alternative.

    http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SQLBill (2/25/2016)


    You should also be able to use ISNUMERIC to see what data is not numeric and therefore can't be converted.

    -SQLBill

    Careful now. ISNUMERIC should never be confused with ISALLDIGITS. It will allow a great many things that, for example, will translate to a MONEY datatype but not to an INT. Please see the following article for 1 of the alternatives...

    http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares (2/27/2016)


    Jeff Moden (2/25/2016)


    Luis Cazares (2/25/2016)


    If by any chance you're working with SQL Server 2012 or later, you can use TRY_CAST or TRY_CONVERT.

    Have you compared it for performance?

    I hadn't test it for performance myself, but I just did. The use of TRY_CAST or TRY_CONVERT uses minimal resources and is almost as fast as not converting the data at all. Using the CASE statement is about 5 times slower than using just functions.

    Here's the quick test that I made. I changed the amount of invalid values but didn't impact the performance.

    WITH

    E(n) AS(

    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

    ),

    E2(n) AS(

    SELECT a.n FROM E a, E b

    ),

    E4(n) AS(

    SELECT a.n FROM E2 a, E2 b

    ),

    cteTally(n) AS(

    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

    FROM E4 a, E2 b

    )

    SELECT CAST( n AS varchar(7))

    + CASE WHEN n < 100000 THEN 'a' ELSE '' END --adding some invalid conversions

    AS nString

    INTO #Test

    FROM cteTally;

    DECLARE @nString varchar(7),

    @nBigInt bigint;

    SET STATISTICS TIME ON;

    PRINT 'Dry run';

    SELECT @nString = nString

    FROM #Test;

    PRINT 'Using TRY_CONVERT';

    SELECT @nBigInt = ISNULL( TRY_CONVERT( bigint, nString), 0)

    FROM #Test;

    PRINT 'Using CASE';

    SELECT @nBigInt = CASE WHEN nString NOT LIKE '%[^0-9]%' THEN CAST(nString AS BIGINT)

    ELSE 0

    END

    FROM #Test;

    SET STATISTICS TIME OFF;

    GO

    DROP TABLE #Test;

    I remember that you mentioned a similar issue when formatting dates using CASE instead of native functions.

    Very cool. I'll try this Monday when I get to work. Thank you, Sir.

    Considering the miserable performance of the new FORMAT function, I'm suspicious of anything new anymore. I really appreciate you putting this together. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'm a bit late to the party, but depending on what version of SQL Server you are on, have you looked at using TRY_CAST insterad of trying to build it yourself with that clunky CASE expression?

    EDIT: Oops, never mind. Now I see that someone else already posted that suggestion. My bad.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Well, until this thread, I'd never considered racing my NumbersOnly ITVF against the new TRY_CONVERT function. We're just migrating one instance to SQL 2012 and it had never occurred to me. Now, however, I have something new to try. They don't have identical functionality, but are similar enough that the curiosity is killing me.

    I honestly hope TRY_CONVERT performs well. Thank you, Luis, for you post causing me me to think of it.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply