CODEWITHSUNDEEP
regex
mohamed hamada
mohamed hamada

Reputation: 574

Regex Oracle Replace After Occurrence nth

I am trying to match before occurrence nth and some how it's working as https://regex101.com/r/wJ9yS6/192

I tried to apply the regex on oracle regex replace 

WITH t AS 
(SELECT 'Home|-|Antennas|¿|Directional Antennas|¿|Gain Horn Antennas|¿|Standard Gain Horn Antennas 
 With Coax Input|¿|10 GHz to 15 GHz WR75 Standard Gain Horn Antennas|¿ WR-75 Waveguide Standard Gain 
Horn Antenna Operating From 10 GHz to 15 GHz With a Nominal 15 dB Gain N Female Input'
mycol
FROM dual)
SELECT
     REGEXP_REPLACE (mycol, '(^(?:[^|]*\|){7})(.*)', '\1')
 FROM t;

but here is what i got full string i do not know why

Upvotes: 1

Views: 133

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

Note that Oracle regex does not support non-capturing groups. You need to use capturing groups instead.

You may use the ^(([^|]*\|){7}).* regex (you do not need the last group here, (.*) can be written as .*):

WITH t AS 
(SELECT 'Home|-|Antennas|¿|Directional Antennas|¿|Gain Horn Antennas|¿|Standard Gain Horn Antennas With Coax Input|¿|10 GHz to 15 GHz WR75 Standard Gain Horn Antennas|¿ WR-75 Waveguide Standard Gain Horn Antenna Operating From 10 GHz to 15 GHz With a Nominal 15 dB Gain N Female Input'
mycol
FROM dual)
SELECT
     REGEXP_REPLACE (mycol, '^(([^|]*\|){7}).*', '\1')
 FROM t

Output (see demo online):

enter image description here

Just note you used a string without line breaks in the regex tester, and your string in the question contains line breaks. You might want to make . match any char, you need to pass n as the 6th argument:

REGEXP_REPLACE (mycol, '^(([^|]*\|){7}).*', '\1', 1, 0, 'n')

Acc. to the docs, n "allows the period character (.) to match the newline character. By default, the period is a wildcard.". 1 will start matching from string start position, and 0 will enable replacing all matches (though here, we only need one, you may use 1 here, too, but it does not matter here).

See another Oracle demo.

Upvotes: 1

Related Questions