Menu
I need to concatenate several columns into one, with spaces between each value. The problem is when one value is null, I end up with a double space between two values.
Example
SELECT (FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME
FROM TABLE_A;
If the middle name happens to be NULL, then I end up with two spaces between the first and last name. Any way to get around this and only have one space when there's a null value?
867
We can concatenate a space character using the || operator. In this example, we have used the || operator to add a space character between the values Dave and Anderson. This will prevent our values from being squished together.
When you concatenate any string with a NULL value, it will result in NULL. To avoid this, you can use the COALESCE function. The COALESCE function returns the first non-Null value. So, when there is a value in the column that is not null, that will be concatenated.
Oracle String concatenation allows you to append one string to the end of another string. To display the contents of two columns or more under the name of a single column, you can use the double pipe concatenation operator (||).
SELECT TRIM(TRIM(FIRST_NAME || ' ' || MIDDLE_NAME) || ' ' || LAST_NAME)
FROM TABLE_A;
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
