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 27I 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).
Error converting data type nvarchar to bigint.
This just means your nvarchar column has some data that doesn't look like a number.
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.
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