Re: regexp_like

From: ddf <oratune_at_msn.com>
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