I am trying to select the records thier 'Text' field match the following form:
__<sql>__ xyz __</sql>__
where __ means anything, and xyz represents the own word that I'm searching for.
Notice: the field 'Text' is a multiline string.
Here is the query, but it doesn't return any records:
Select * from reports where regexp_like (text, '(.*)<sql>(.*)(^|\s)xyz(\s|$)(.*)</sql>(.*)', 'i');
You used 'i' as the third argument to REGEXP_LIKE, to make the search case-insensitive.
Change that to 'in'. The additional n is another option to REGEXP functions: it tells the regular expression engine to allow . to match chr(10) (newline). The default behavior, without this option, is for . to not match newline.
For this setup the plain old LIKE works fine. You need not even care about the new line charactes.
where column1 LIKE '%<sql>%xyz%</sql>%'
will do the job.
For a case insensitive search add LOWER
where LOWER(column1) LIKE '%<sql>%xyz%</sql>%'
For a context delimiter search, e.g. whole word search simple replace all relevant white space characters to blank:
where replace(replace(lower(column1),CHR(10),' '),CHR(9),' ') LIKE '%<sql>% xyz %</sql>%';
Here I'm replacing new line and tab, feel free to add other, e.g. CHR(13) if required.
General rule is - if you may use LIKE prefer it to REGEXP as the performance of LIKE is much better.
Simple demo:
create table MYTABLE(column1 varchar2(1000))
;
insert into MYTABLE(column1) values('bla bla <sql> bla'||chr(10)||' xyz bla </sql> bla');
insert into MYTABLE(column1) values('bla bla <sql> bla'||chr(10)||' NO MACTH bla </sql> bla');
commit;
select * from MYTABLE;
COLUMN1
-------------------
bla bla <sql> bla
xyz bla </sql> bla
bla bla <sql> bla
NO MACTH bla </sql> bla
select * from MYTABLE
where column1 LIKE '%<sql>%xyz%</sql>%';
COLUMN1
-------------------
bla bla <sql> bla
xyz bla </sql> bla
