CODEWITHSUNDEEP
sqloracle-databaseselect
Jimmy
Jimmy

Reputation: 16428

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

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

Upvotes: 6

Views: 10499

Answers (3)

alexeionin
alexeionin

Reputation: 514

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

Upvotes: 5

SGB
SGB

Reputation: 636

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

Upvotes: 3

Yahia
Yahia

Reputation: 70369

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

Upvotes: 9

Related Questions