Using Oracle regexp_replace to capture multiple strings or words

Question

I have data in a line like this.

age:32, name:Michael John, country:USA, age:34, name:Lewis Carl, country:USA, age:53, name:Larry Moore, country:Mexico

And I would like to capture all the names from above line.

expected output:-

Michael John, Lewis Carl, Larry Moore

How can I achieve that?

Please take note I show 3 datasets but the dataset is dynamic and can be more than 3. So the expected name list can be more than 3.

I have tried this way

select regexp_replace(s1,'(name:\w+( +\w+)*)','<\1>') from ( select 'age:32, name:Michael John, country:USA, age:34, name:Lewis Carl, country:USA, age:53, name:Larry Moore, country:Mexico' s1 from dual ) ;

here is the output

age:32, <name:Michael John>, country:USA, age:34, <name:Lewis Carl>, country:USA, age:53, <name:Larry Moore>, country:Mexico

But I have no idea how to remove the "age" and "country" that I would like to omit.

Please advise.

What you show is not "three datasets" - it's only a single row with three occurrences of "name". Right? Or are those really three rows (three input strings), with a single name in each? If it's a single input string with three names in it - do you always have only ONE string to process, or will you need to process several strings ("lines") - with varying numbers of "names" in each string - at the same time? And, what is your Oracle version? — user5683823
– user5683823, Commented Dec 10, 2020 at 15:37
You re right. It is always single line. And the key 'name' will appear multiple times but not limit to 3. My bad if I use term dataset wrongly. My oracle version is 12cr2. — user3367504
– user3367504, Commented Dec 10, 2020 at 23:39

MT0 · Accepted Answer · 2020-12-11 00:17:18Z

0

SELECT RTRIM( REGEXP_REPLACE( value, '\s*(name:([^,]+,?)|[^,]+,?)', '\2' ), ',' ) AS names
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'age:32, name:Michael John, country:USA, age:34, name:Lewis Carl, country:USA, age:53, name:Larry Moore, country:Mexico'
FROM   DUAL;

Outputs:

| NAMES                               |
| :---------------------------------- |
| Michael John,Lewis Carl,Larry Moore |

db<>fiddle here

edited Dec 11, 2020 at 0:17

answered Dec 10, 2020 at 15:42

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

user3367504 Over a year ago

Thanks a lot. It works. Mind to explain how the reg exp works? I am just new to learn about it.

Collectives™ on Stack Overflow

Using Oracle regexp_replace to capture multiple strings or words

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related