CODEWITHSUNDEEP
sqloracle-database
tokyo_north
tokyo_north

Reputation: 125

RTRIM inside LISTAGG?

Good day. I am writing a query that uses LISTAGG and returns results. This is the code I have so far.

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

I expect that this returns results, aggregates them, and trims off 1 character from the right on shop_cde. However, it appears no trimming occurs. Shop_cde still shows in full. Does anyone know how to TRIM inside a LISTAGG function?

Upvotes: 2

Views: 1017

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

The trim() functions are generally used to remove leading and trailing spaces (although you can remove other characters as well).

If you want to discard the last character, use 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

Upvotes: 3

collapsar
collapsar

Reputation: 17238

Use substr if you want to remove a given number of charcaters from the right, use rtrim if you want an unspecified number of a given character eliminated. Removing on the left would use substr(..., 2) and ltrim, resp.

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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

you should use rtrim(listagg(....) ) 

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

Upvotes: 0

Related Questions