CODEWITHSUNDEEP
oracle-databaseoracle11g
Aj here
Aj here

Reputation: 47

REMOVE THE LAST COMMA in oracle

COUNTNUM is a column name in a table that has data like below 1,2,3,4,

I used

RTRIM((COUNTNUM),',')  COUNTNUM

It didn't work

Desired output 1,2,3,4

Current output 1,2,3,4,

Any suggestions would greatly help..! Thanks

Upvotes: 0

Views: 7744

Answers (2)

Patrick Bacon
Patrick Bacon

Reputation: 4640

Perhaps There are non-digits after the comma which needed to be removed

Logic is added to account for possible non-digits between the comma and the end of countnum.

Explanation:

[^[:digit:]] is the negation of the digit character class

* is a quantifier meaning zero to many

$ is an anchor identify the end of countnum

SCOTT@dev>WITH d AS (
  2      SELECT
  3          '1,2,3,4, ' countnum
  4      FROM
  5          dual
  6      UNION ALL
  7      SELECT
  8          '1,2,3,4,'
  9      FROM
 10          dual
 11  ) SELECT
 12      countnum,
 13      regexp_replace(
 14          countnum,
 15          ',[^[:digit:]]*$'
 16      ) mod_count_num
 17  FROM
 18      d;

COUNTNUM       MOD_COUNT_NUM  
1,2,3,4,       1,2,3,4        
1,2,3,4,       1,2,3,4

Upvotes: 0

Hellmar Becker
Hellmar Becker

Reputation: 2972

REGEXP_REPLACE((countnum), ',$', '')

Upvotes: 2

Related Questions