CODEWITHSUNDEEP
sqlregexdatabaseoracle-database
user7151558
user7151558

Reputation:

Need help removing the last comma from the query

I have a following query which returns me open action owner's name and removes duplicate record from comma separated values with the help of regex. 

OpenActionUsers
  AS ( SELECT DISTINCT action.INVESTIGATION_ID
            , REGEXP_REPLACE(
                LTRIM(
                  MAX(
                    SYS_CONNECT_BY_PATH(person.Last_Name || ' ' || person.First_Name || ', ', '<br/>')
                  )), 
              '([^,]+)(,[ ]*\1)+|(?<=(.*?, )\1)(.+?)<[^>]+>|\&(nbsp;)|(amp;)([^ ]+)( [ ]*\2)+', '\1') AS OPEN_ACTION_USERS
            , LISTAGG(userInfo.USER_ID, ', ') WITHIN GROUP (ORDER BY userInfo.USER_ID) AS OPEN_ACTION_USER_IDS
         FROM OpenActionInfo action
         JOIN ETK_User userInfo ON action.ACTION_OWNER = userInfo.USER_ID
         JOIN ETK_Person person ON userInfo.Person_Id = person.Person_Id
         GROUP BY action.INVESTIGATION_ID
         CONNECT BY action.INVESTIGATION_ID = PRIOR action.INVESTIGATION_ID AND RN = PRIOR RN + 1 START WITH RN = 1
     )

For example, this is what my table column "action_owner_name " currently looks like:

action_owner_name
------------------
Sam Williams,
Tom White,
Dan Andrews,
Chris Thomas, Sam Williams,
John Johnson, Dan Adnrews, Sean Gates,

What can I do to remove the comma from a single record and also from the last record if there's multiple records?

I want my desire output to look like this:

action_owner_name
------------------
Sam Williams
Tom White
Dan Andrews
Chris Thomas, Sam Williams
John Johnson, Dan Adnrews, Sean Gates

Upvotes: 0

Views: 662

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Since each term has one extra comma at the end, you could substring it off:

SELECT
    SUBSTR(OPEN_ACTION_USERS, 1, LENGTH(OPEN_ACTION_USERS) - 1) AS OPEN_ACTION_USERS,
    OPEN_ACTION_USER_IDS
FROM OpenActionUsers

Upvotes: 2

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Just add a RTRIM(...,',')..

RTRIM(
        LTRIM(
            MAX(SYS_CONNECT_BY_PATH(person.Last_Name || ' ' || person.First_Name || ', ', '<br/>')
        ),
        ','
    )

Upvotes: 3

Related Questions