Menu
I've got the following reference data.
PERSON_NAME STREET TOWN COUNTY POSTCODE
------------------------------ ------------------------ ---------------- ---------------- ----------
David Smith 30 Johnson Street Norwich Norfolk NA38 3KL
John Brown Douglas Road Cambridge C8 9IJ
Jackie White 8 High Street Ipswich Suffolk IP7 2YT
Andrea Blue 9 Marlborough Ave Bury Suffolk IP4 0XC
Jemima Green Riverside Walk Colchester Essex CO6 7PR
James Gray 167 Hadleigh Place London SW1 4TU
What I want to do, is to display a list of person names, along with their addresses concatenated into a comma separated string.
This part is easy, I have used the || to concat columns and place comma separators.
The part I'm in question over, is the fact that some rows don't have anything listed for COUNTY, therefore I need to avoid displaying , ,.
I've done some research for myself, and have decided to use the SUBSTR in Oracle to replace double commas, however it does feel slightly "dirty". Is there a cleaner way of doing this, avoiding the use of complex functions (such as this previous SO question)?
This is what I have :
SELECT
SUPPNAME as "Supplier Name",
REPLACE(STREET || ', ' || TOWN || ', ' || COUNTY || ', ' || POSTCODE, ' ,','') as "Supplier Address"
FROM
SUPPLIERS
;
Thanks
371
Concatenating Data When There Are NULL Values To resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space. We can verify the output of the data as shown below.
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL . When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
SQL Server CONCAT() Function The CONCAT() function adds two or more strings together.
SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table; Output: Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(' ') in CONCAT() function. This method will change the original table.
try
SELECT
SUPPNAME AS "Supplier Name",
(
CASE WHEN STREET IS NULL THEN '' ELSE STREET || ', ' END ||
CASE WHEN TOWN IS NULL THEN '' ELSE TOWN || ', ' END ||
CASE WHEN COUNTY IS NULL THEN '' ELSE COUNTY || ', ' END ||
CASE WHEN POSTCODE IS NULL THEN '' ELSE POSTCODE END
) AS "Supplier Address"
FROM SUPPLIERS
In previous answers if all fiields is NULL then you will get only stupid ', ' instead expected NULL. Try this approach to remove one extra ', ' at start of result
SELECT
SUPPNAME AS "Supplier Name",
SUBSTR(
NVL2(STREET, ', ' || STREET, NULL)
|| NVL2(TOWN, ', ' || TOWN, NULL)
|| NVL2(COUNTY, ', ' || COUNTY, NULL)
|| NVL2(POSTCODE, ', ' || POSTCODE, NULL)
,2) AS "Supplier Address"
FROM SUPPLIERS
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
