0

I have a String with multiple * separated items that I'm trying to convert into rows. Example:

Text 'Sometext * Apple * Corn' would result into 3 rows

Sometext
Apple
Corn

I was able to find a comma into rows regexp expression but I haven't been able to do the same with asterisk.

WITH my_text AS
(
 SELECT 'Sometext*Apple*Corn' my_text 
   FROM dual
)
 SELECT TRIM(REGEXP_SUBSTR(my_text, '[^,]+', 1, level)) AS my_text
   FROM my_text
CONNECT BY level <= LENGTH(my_text) - LENGTH(replace(my_text, ',')) + 1

Does anybody know what I need to change to get it to work please?

Thanks

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
JB999
  • 441
  • 3
  • 16
  • 1
    Your expression works for separator `,`, while you have `*` in your string. Just replace `,` with `*` in `regexp_substr` and in `length` and it might just work. https://dbfiddle.uk/qv1K2XDh – GMB Nov 16 '22 at 21:59

0 Answers0