Re: regexp_like
Date: Mon, 3 Nov 2008 08:10:47 -0800 (PST)
Message-ID: <7af5b570-c998-47e8-a991-cc10cde82a73@d10g2000pra.googlegroups.com>
On Nov 3, 8:40 am, carlos.ohigg..._at_yahoo.com wrote:
> Hi,
> I want to run a distinct query against a text field."something,
> something else, etc, etc,"
> I have got this far:
> SELECT distinct (Summary), count( distinct (Summary)) FROM report
> where
> Cost> 1000 group by Summary;
>
> But this is looking at whole text field, and I just want to use the
> first portion of the comma separated text field.
> I expect regexp_like is the solution but I cannot quite figure it
> out!
>
> I think just a few hints would help me crack this one!
>
> TIA
>
> Carlos
I don't know that regexp_like is the solution to this problem, as it has no provision (as far as I am aware) to restrict the text searched by the regular expression. You could, however, use substr() and instr() to generate that subset of your text:
SELECT distinct (substr(Summary, 1, instr(Summary, ','))) Summary,
count(*) ct
FROM report
where Cost> 1000
group by distinct (substr(Summary, 1, instr(Summary, ',')));
To illustrate:
SQL> create table text_tst(
2 l_idx number, 3 l_txt varchar2(4000), 4 cost number
5 );
Table created.
SQL>
SQL> insert into text_tst
2 values (1, 'This is a test, only a test, and nothing but a
test.', 973);
1 row created.
SQL> insert into text_tst
2 values (2, 'This is not a test, but only a test, and nothing but
a test.', 1104);
1 row created.
SQL> insert into text_tst
2 values (3, 'This is a test of note, only a noteworthy test, and
nothing but a test.', 1702);
1 row created.
SQL> insert into text_tst
2 values (4, 'This is not a noteworthy test, only a test of notable
note, and nothing but a test.', 3887);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select *
2 from text_tst;
L_IDX
L_TXT
COST
1 This is a test, only a test, and nothing but a test. 973 2 This is not a test, but only a test, and nothing but a test. 1104 3 This is a test of note, only a noteworthy test, and nothing but a test. 1702 4 This is not a noteworthy test, only a test of notable note, and nothing but a test. 3887
SQL>
SQL> select l_txt, count(*) ct
2 from text_tst
3 where regexp_like(l_txt, '^This is not')
4 and cost > 1000
5 group by l_txt;
L_TXT
CT
This is not a test, but only a test, and nothing but a
test. 1 This is not a noteworthy test, only a test of notable note, and nothing but a test. 1
SQL>
SQL> select substr(l_txt, 1, instr(l_txt, ',')) l_txt, count(*) ct
2 from text_tst
3 where substr(l_txt, 1, instr(l_txt, ',')) like '% not %'
4 and cost > 1000
5 group by substr(l_txt, 1, instr(l_txt, ','));
L_TXT
CT
This is not a
test,
1
This is not a noteworthy
test,
1
SQL> Note the first example, with regexp_like searches from the start of the string but this requires that you know what you're searching for at that point in the text. The second example restricts the text string to the N characters up to the first comma.
It's a thought.
David Fitzjarrell Received on Mon Nov 03 2008 - 10:10:47 CST