Extract third value from a semicolon separated string
create table tq84_regexp_substr (
col_1 varchar2(30)
);
insert into tq84_regexp_substr values ('abc;def;ghi;klm');
-- Extract the third value
select regexp_substr(col_1, '[^;]+[^;]', 1, 3) third from tq84_regexp_substr;
--
-- ghi
--
-- Same thing with alternative regexp
select rtrim(regexp_substr(col_1, '.*?;' , 1, 3), ';') third from tq84_regexp_substr;
--
-- ghi
drop table tq84_regexp_substr purge;
Extract the portion of a text between two delimiters
The following example extracts the portion of the text that is delimeted with > and <.
declare
str varchar2(100) := 'foo bar >words and such< baz';
begin
dbms_output.put_line(regexp_substr(
str , -- Text
'[^><]+', -- Any repetation of characters that don't match the delimiters > and <
1 , -- Start at first position
2 -- Second occurence (The first is before >)
)
);
end;
/
This example uses the regular expression pattern \w+ in combination with a recursive query to extract «words» from a text. Each word is returned as a record:
select
regexp_substr('
This regexp_replace example returns a
record for each word in this text', '\w+', 1, level) word
from
dual connect by level <= 12 --<== needs to be adjused or result set to be filtered.
;
--
-- WORD
-- ------------------------------------------------------------------------
-- This
-- regexp_replace
-- example
-- returns
-- a
-- record
-- for
-- each
-- word
-- in
-- this
-- text
See also
regexp_substr() can be used to translate CSV lines to individual columns, see here.