Return first not null value

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.




https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

Msg 457, Level 16, State 1, Line 15 Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

   
Union ALL across 2 databases was failing with above error.


solution is to identify the column which is causing this issue and use collate database_default

select ColumnCasuingIssues collate database_default 
from myDatabase1.dbo.MyTable1

Union ALL

select ColumnCasuingIssues collate database_default 
from myDatabase2.dbo.MyTable1

Error converting data type varchar to float. - Try_Cast() - Try_Parse() - Try_convert()

Error was happening when SQL server is trying to join a float column with varchar and it tries to convert varchar to float explicitly.

I tried to handle it using IsNumeric function but there was '.' dot present in my data set which lead to above error.

SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('.') as '.' --Period
 ,ISNUMERIC(',') as ',' --Comma

Function ISNUMERIC() returns "1" when the input expression evaluates to a valid numeric data type; otherwise it returns "0". But the above query will return value "1" for all 3 column values, validating them as numeric values, but that's not correct for last 2 columns.

And not only this, ISNUMERIC() function treats few more characters as numeric, like: - (minus), + (plus), $ (dollar), \ (back slash), check this:

SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('abc') as 'abc'
 ,ISNUMERIC('-') as '-'
 ,ISNUMERIC('+') as '+'
 ,ISNUMERIC('$') as '$'
 ,ISNUMERIC('.') as '.'
 ,ISNUMERIC(',') as ','
 ,ISNUMERIC('\') as '\'

This will return "0" for second column containing value "abc", and value "1" for rest of the column values.

So, you will need to be very careful while using ISNUMERIC() function and have to consider all these possible validations on your T-SQL logic.

- OR -

Switch to new TRY_PARSE()  or TRY_CAST() or TRY_CONVERT() function introduced in SQL Server 2012.

above behavior of isNumeric function was causing the above error.

handled the logic using 

=> The TRY_PARSE() function returns the result of an expression, translated to the requested Data-Type, or NULL if the Cast fails. Let's check how TRY_PARSE() validates above character values as numeric:

SELECT 
 TRY_PARSE('123' as int) as '123'
 ,TRY_PARSE('abc' as int) as 'abc'
 ,TRY_PARSE('-' as int) as '-'
 ,TRY_PARSE('+' as int) as '+'
 ,TRY_PARSE('$' as int) as '$'
 ,TRY_PARSE('.' as int) as '.'
 ,TRY_PARSE(',' as int) as ','
 ,TRY_PARSE('\' as int) as '\'

or

TRY_CONVERT(INT,Number)
or

references

https://blogs.msdn.microsoft.com/manub22/2013/12/23/use-new-try_parse-instead-of-isnumeric-sql-server-2012/