TRY_PARSE but faster

Question

What would be the quickest way of transforming a large number (almost 100) of nvarchar columns to floats?

At the moment I have a bunch of TRY_PARSE(column-name AS FLOAT) statements, one per column.

Where a column does not parse to float, I want NULL. No error notifications, no extra intelligence.

Is there a quicker way? If so, what would it be?

SQL Server 2012

Answer 1

Just a short test to show the performance difference between TRY_PARSE and TRY_CAST

My result

• TRY_CAST 327 ms
• TRY_PARSE 17784 ms

That means, that TRY_CAST is about 50 times faster than TRY_PARSE (btw: TRY_CONVERT is almost the same as TRY_CAST in performance).

Try it out and post your results ;-)

SET LANGUAGE ENGLISH;

DECLARE @tbl TABLE(SomeFloatAsString VARCHAR(100),fl2 VARCHAR(100),fl3 VARCHAR(100),fl4 VARCHAR(100),fl5 VARCHAR(100),fl6 VARCHAR(100),fl7 VARCHAR(100),fl8 VARCHAR(100),fl9 VARCHAR(100),fl10 VARCHAR(100));
DECLARE @i INT=0;
WHILE @i<100000
BEGIN
    INSERT INTO @tbl VALUES(RAND(),RAND(),RAND(),RAND(),RAND(),RAND(),RAND(),RAND(),RAND(),RAND());
    SET @i+=1;
END

DECLARE @d DATETIME2 = SYSUTCDATETIME();

SELECT TRY_CAST(SomeFloatAsString AS FLOAT) fl1
      ,TRY_CAST(fl2 AS FLOAT) fl2
      ,TRY_CAST(fl3 AS FLOAT) fl3
      ,TRY_CAST(fl4 AS FLOAT) fl4
      ,TRY_CAST(fl5 AS FLOAT) fl5
      ,TRY_CAST(fl6 AS FLOAT) fl6
      ,TRY_CAST(fl7 AS FLOAT) fl7
      ,TRY_CAST(fl8 AS FLOAT) fl8
      ,TRY_CAST(fl9 AS FLOAT) fl9
      ,TRY_CAST(fl10 AS FLOAT)fl10
INTO #tmp1
FROM @tbl

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME())


DECLARE @d2 DATETIME2 = SYSUTCDATETIME();

SELECT TRY_PARSE(SomeFloatAsString AS FLOAT) fl1
      ,TRY_PARSE(fl2 AS FLOAT) fl2 
      ,TRY_PARSE(fl3 AS FLOAT) fl3
      ,TRY_PARSE(fl4 AS FLOAT) fl4
      ,TRY_PARSE(fl5 AS FLOAT) fl5
      ,TRY_PARSE(fl6 AS FLOAT) fl6
      ,TRY_PARSE(fl7 AS FLOAT) fl7
      ,TRY_PARSE(fl8 AS FLOAT) fl8
      ,TRY_PARSE(fl9 AS FLOAT) fl9
      ,TRY_PARSE(fl10 AS FLOAT)fl10
INTO #tmp2
FROM @tbl

SELECT DATEDIFF(MILLISECOND,@d2,SYSUTCDATETIME());