Friday, May 18, 2012

Joins fail converting nvarchar/varchar to int/numeric


Quick Description:  Attempting a join on two columns, one nvarchar, and one a numeric type, fails with error
Msg 8114, Level 16, State 5, Line 27
Error converting data type nvarchar to bigint. 

This just means your nvarchar column has some data that doesn't look like a number. 
I generally have to compensate for this when I'm joining a table that has nvarchar data that is meant to reference a key in  (in my case, it's usually trying to join tables with a 'liberal' policy on keys to a conformed dimension) a table with a key of a numeric type.  In my experience, the problem is usually variations on words like unassigned or unknown.  Since there might not be a strict policy on what goes into the table it's hard to anticipate what character strings to exclude (and often messy even if you do).
The obvious question is why doesn't sql have something like C# TryParse, and I tried to put something similar together below, but just quickly, if you want to join on two columns, one of which might have stray character data that should be discarded (it wasn't going to join anyway) .  You can use the kinda-sorta half of TryParse  'ISNUMERIC' function to test you can convert it to a number 'during' the join.  I'm sure there are lots of ways to do this, many of which are significantly more elegant, your mileage may vary.
Example Solution:
SELECT N.NUMBERS_ONLY, V.HAS_SOME_STRAY_STRINGS FROM TABLE_WITH_A_NUMERIC_COLUMNN N    JOIN TABLE_WITH_SOME_WIERD_STUFF_IN_THAT_COLUMN V
                    ON N.NUMBERS_ONLY = V.HAS_SOME_STRAY_STRINGS AND ISNUMERIC(V.HAS_SOME_STRAY_STRINGS) = 1
 

Straight Select:  If what you need to do is return a column

SELECT STRAYSTRINGS_TO_NUM =
    CASE 
        WHEN ISNUMERIC(COLUMN_WITH_SOME_STRINGS)= 1 THEN CONVERT(INT, COLUMN_WITH_SOME_STRINGS)
    ELSE
         'DO SOMETHING ELSE'
    END      
FROM TABLE_WITH_SOME_WEIRD_STUFF

Sort of like TryParse:  In case what you really need is to convert a value and the report success or failure of the conversion, this is what I came up with for a basic equivalent of c# TryParse.  You could

DECLARE @TRYPARSE_VAR nvarchar(255) = '123456'SELECT @TRYPARSE_VAR = RTRIM(LTRIM(@TRYPARSE_VAR))
IF ISNUMERIC(@TRYPARSE_VAR) = 1
    BEGIN
        SELECT @TRYPARSE_VAR = CONVERT(INT, @TRYPARSE_VAR)
        SELECT 1
    END
    ELSE
    BEGIN
        SELECT 0
    END

Or you could not bother with ISNUMERIC and just use a try/catch

DECLARE @TRYPARSE_VAR nvarchar(255) = '123456'SELECT @TRYPARSE_VAR = RTRIM(LTRIM(@TRYPARSE_VAR))
BEGIN TRY

        SELECT @TRYPARSE_VAR = CONVERT(INT, @TRYPARSE_VAR)
        SELECT 1
END TRY
BEGIN CATCH
        SELECT 0
END CATCH    

No comments:

Post a Comment