LISTAGG内部的RTRIM？

Question

日安。我正在编写一个使用LISTAGG并返回结果的查询。这是我到目前为止掌握的密码。

   select 
    listagg(rtrim(shop_cde, 1), ', ') within group (order by shop_cde) col1,
    business_cde
    from mytable
    group by business_cde

我希望这会返回结果，聚合结果，并从shop_cde的右侧剪掉一个字符。然而，它似乎没有发生修整。Shop_cde仍然充分显示。有人知道如何在LISTAGG函数内修剪吗？

Answer 1

trim()函数通常用于删除前导和尾随空格(尽管您也可以删除其他字符)。

如果要丢弃最后一个字符，请使用substr()

select listagg(substr(shop_cde, 1, length(shop_cde) - 1), ', ') within group (order by shop_cde) col1,
       business_cde
from mytable
group by business_cde

Answer 2

如果要从右边移除给定数量的字符，请使用substr；如果希望删除未指定数目的给定字符，请使用rtrim。左边移除将使用substr(..., 2)和ltrim，resp。

   select 
    listagg(substr(shop_cde, -1), ', ') within group (order by shop_cde) col1,
    business_cde
    from mytable
    group by business_cde

Answer 3

你应该使用rtrim(listagg(.))

select 
rtrim(listagg(shop_cde, ', ') within group (order by shop_cde) ) col1,
business_cde
from mytable
group by business_cde