SQL Server NULL Integer to Empty String using ISNULL

sql sql-server tsql string isnull
Ahz picture Ahz · Aug 19, 2014 · Viewed 9k times · Source

My curiosity always gets the best of me and I've searched online for an explanation to this and came up with nothing (could be because I didn't use the right terms.)

Can someone please explain why SQL Server returns a value of zero (0) when the following is executed, instead of an empty string ('').

    DECLARE @I AS INT
    SET @I = NULL
    SELECT ISNULL(@I, '') -- 0

Answer

hunch_hunch picture hunch_hunch · Aug 19, 2014

As declared here, the second argument to ISNULL is the replacement_value, which "must be of a type that is implicitly convertible to the type of check_expresssion." Implicitly converting '' to INT results in 0.

Related questions

How do I remove the first characters of a specific column in a table?

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for …

Read More
Better techniques for trimming leading zeros in SQL Server?

I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to …

Read More
IndexOf function in T-SQL

Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in T-SQL? Looking for something that returns the position of a substring within a string. in C# …

Read More