CODEWITHSUNDEEP
regexoracle-databaseplsqloracle11g
Emma
Emma

Reputation: 27

ORACLE REGEX to add delimiter after every file name

I have got few files which are by separated by multiple spaces, I wrote a regex which would remove all the white spaces but not able to add the delimiter after end of every file. Can you please assist?

select regexp_replace(file,'[[:space:]]',',') from test_table
where id in ('1','2');

The above select only works if there is a single space between files.

  1. Please note that files can be of any extension, also i see a few (.) appearing in between file names.
  2. spaces are uneven in the data

sample data

filename :
this is the file sent.today .msg required mail.msg
aa.docx bb.docx cc.pdf

required output (delimiter added(,) after each file

filename :
this is the file sent.today .msg, required mail.msg
aa.docx, bb.docx, cc.pdf

Upvotes: 0

Views: 137

Answers (2)

momolechat
momolechat

Reputation: 162

regexp_replace(data,'(\.[a-zA-Z]+) ([a-zA-Z])','\1, \2')

Each group (behind parenthesis) in the regex will be matched to their number in the resulting string (ie \1 and \2)

Note also that this regex does not work for file type containing a number.

Upvotes: 1

user5683823
user5683823

Reputation:

You explained that you can consolidate whitespace to single spaces. That has to be done separately from the "adding of commas" anyway, so let's say that's done.

You need a second REGEXP pass on the result of the first. Something like this:

regexp_replace(result_of_first_pass, '\.(docx|pdf|msg) ', '.\1, ')

Upvotes: 1

Related Questions