Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

select

oracle

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

like image 371
Jimmy Avatar asked Nov 26 '11 17:11

Jimmy


People also ask

How do I concatenate NULL columns in SQL?

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.

Can we concatenate string and NULL in SQL?

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).

Which is the correct way to concatenate 2 strings in SQL?

SQL Server CONCAT() Function The CONCAT() function adds two or more strings together.

How do I concatenate two columns in SQL Select statement?

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.


2 Answers

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
like image 54
Yahia Avatar answered Nov 15 '22 06:11

Yahia


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
like image 34
alexeionin Avatar answered Nov 15 '22 06:11

alexeionin