February 25, 2016 at 12:03 pm
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
February 25, 2016 at 12:14 pm
Have a look at the script here
February 25, 2016 at 12:17 pm
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.
February 25, 2016 at 12:27 pm
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://qa.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.
February 25, 2016 at 12:39 pm
You should also be able to use ISNUMERIC to see what data is not numeric and therefore can't be converted.
-SQLBill
February 25, 2016 at 12:46 pm
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.
February 25, 2016 at 8:28 pm
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
February 27, 2016 at 5:40 pm
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.
February 27, 2016 at 6:32 pm
dogramone (2/25/2016)
Have a look at the script here
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://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/
--Jeff Moden
February 27, 2016 at 6:34 pm
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://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/
--Jeff Moden
February 27, 2016 at 6:37 pm
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
February 28, 2016 at 2:37 am
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.
February 28, 2016 at 6:34 am
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