Menu
I have this select statement where I check whether phone number in null or empty and if they are then I would return 'No Phone Number is available'. Like this
SELECT
Name,
ISNULL(NULLIF(Phone, ''), 'No Phone Number is available') AS Phone
FROM Person
But when the phone number is null or empty I am not getting the full text 'No phone number is available'. Only the first 20 characters is getting returned. The length of Phone field is also 20. So I think this is returning text depending on the length of the phone field.
Is there a way to correct this without changing the field length?
486
ISNULL( ) function replaces the Null value with placed value. The use of ISNULL ( ) function is very common in different situations such as changing the Null value to some value in Joins, in Select statement etc. NULLIF ( ) function returns us Null if two arguments passed to functions are equal.
COALESCE function in Teradata returns NULL if all arguments evaluate to null; otherwise it returns the value of the first non-null argument. NULLIF is to used evaluate two expressions and returns NULL if the two arguments are equal otherwise if returns the first arguments.
The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.
ISNULL replaced the Oracle NVL function in the SQL server. When an expression in SQL server is NULL, the ISNULL function allows you to return an alternative value for the null.
You are correct.
ISNULL uses the datatype and length of the first parameter. COALESCE takes the "highest precedence" one. So:
COALESCE(NULLIF(Phone, ''), 'No Phone Number is available') AS Phone
Or
ISNULL(NULLIF(CAST(Phone as varchar(30)), ''), 'No Phone Number is available') AS Phone
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
