SQL选择；连接字符串，避免在列为空时使用双逗号？

发布于 2024-12-18 12:43:11 字数 1568 浏览 1 评论 0原文

我有以下参考数据。

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

我想要做的是显示人名列表，以及将他们的地址连接成逗号分隔的字符串。

这部分很简单，我使用 || 来连接列并放置逗号分隔符。

我有疑问的部分是，某些行没有为 COUNTY 列出任何内容，因此我需要避免显示 , ,。

我自己做了一些研究，并决定使用 SUBSTR在Oracle中替换双逗号，但是它确实感觉有点“脏”。有没有更干净的方法来做到这一点，避免使用复杂的函数（例如这个之前的问题）？

这就是我所拥有的：

SELECT
    SUPPNAME as "Supplier Name",
    REPLACE(STREET || ', ' || TOWN || ', ' || COUNTY || ', ' || POSTCODE, ' ,','') as "Supplier Address"
FROM
    SUPPLIERS
;

谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏尔 2024-12-25 12:43:11

尝试

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

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

回复收藏 0 原文

复古式 2024-12-25 12:43:11

在前面的答案中，如果所有字段都为 NULL，那么您只会得到愚蠢的 ', ' 而不是预期的 NULL。尝试这种方法来删除结果开头的一个额外的“，”

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

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

回复收藏 0 原文