SQL Select; Concatenating strings, avoiding double commas where columns are null?

Question

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

score 9 · Accepted Answer · answered Nov 26 '11 at 18:05

9

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

answered Nov 26 '11 at 18:05

Yahia

69,653
9
115
144

2

I deleted my answer because I found that in Oracle string concats with NULLs does not yield a null (as I believe it does in SQL Server), just as you mentioned. I started to switch to case statements, but then saw your answer - +1 – Jake Feasel Nov 26 '11 at 18:11
This leaves a , comma at the end. So I would do it like this. `SELECT SUPPNAME AS "Supplier Name", REGEXP_REPLACE( 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` – devonuto Apr 18 '19 at 00:49

score 5 · Answer 2 · answered Apr 24 '15 at 15:20

5

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

answered Apr 24 '15 at 15:20

alexeionin

514
4
8

This worked perfectly for me and I avoided trailing commas. – Matt Koch Sep 22 '16 at 15:29

score 3 · Answer 3 · answered Nov 26 '11 at 18:12

3

You could use NVL2 around the fields that could be null, somthing like NVL2(county, county || ',', '')

answered Nov 26 '11 at 18:12

SGB

616
6
10

SQL Select; Concatenating strings, avoiding double commas where columns are null?

3 Answers3