sql length(null)

Question

I have two columns: p.firstName and p.lastName.

Now I want to display the length of both names combined, the problem is, that in some fields of p.lastName the value "null" is stored.

When I try to do it like this:

LENGTH(p.firstName + p.lastName)

I get an error:

ORA-01722 invalid number

However, if I split it up

LENGTH(p.firstName) + LENGTH(p.lastName)

it doesn't give me an error message. But the length of the sum is NULL, even if the firstName does have a few characters. The NULL of p.LastName breaks it so to say.

Anyone has an idea?

Answer 1

You can Use IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

SELECT LENGTH(COALESCE(p.firstName,'') + COALESCE(p.lastName,'')) AS Len FROM TableName

OR

SELECT LENGTH(ISNULL(p.firstName,'') + ISNULL(p.lastName,'')) AS Len FROM TableName

OR

SELECT LENGTH(IFNULL(p.firstName,'') + IFNULL(p.lastName,'')) AS Len FROM TableName

OR

SELECT LENGTH(NVL(p.firstName,'') + NVL(p.lastName,'')) AS Len FROM TableName

OR

SELECT LENGTH(p.firstName || p.lastName) AS Len FROM TableName