Oracle SQL query to remove whitespace before comma

Question

I have a comma separated string like

This,is ,a,beautiful ,summer day

Can you please help me remove the WHITESPACE before "comma", without removing the space between two words?

Tried below query:

SELECT regexp_replace('This,is ,a,beautiful  ,summer day'  , '[[:space:]]', null ) regexp
  FROM dual;

but it's removing the space between summer day.

Output:

This,is,a,beautiful,summerday

MT0 · Accepted Answer · 2021-03-23 13:36:15Z

2

You can replace multiple spaces using:

SELECT regexp_replace(
         'This,is ,a,beautiful  ,summer day'  ,
         '[[:space:]]+,',
         ','
       ) regexp
FROM   dual;

Which outputs:

| REGEXP                         |
| :----------------------------- |
| This,is,a,beautiful,summer day |

db<>fiddle here

answered Mar 23, 2021 at 13:36

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2021-03-23 13:23:55Z

1

As you said - replace it:

SQL> with test (col) as
  2    (select 'This,is ,a,beautiful ,raining day' from dual)
  3           --This,is,a,beautiful,summerday
  4  select replace(col, ' ,', ',') result                 --> this
  5  from test;     

RESULT
-------------------------------
This,is,a,beautiful,raining day

SQL>

answered Mar 23, 2021 at 13:23

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

Oracle SQL query to remove whitespace before comma

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related