Home » SQL & PL/SQL » SQL & PL/SQL » Search string in a CLOB
Search string in a CLOB [message #48147] Fri, 22 October 2004 03:58 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hi you all,

I have a table with a CLOB column. The text in the CLOB looks like

hello, this is my clob and my id number is [[12345678]].
And I hope someone can help me out :-)...and this is the end of my CLOB.

What I like to do is search in the CLOB for the opening bracket and closing bracket...get the string within the brackets.

Is this possible? Can someone provide me an example?

Thanks in advance!

Report message to a moderator

Re: Search string in a CLOB [message #48149 is a reply to message #48147] Fri, 22 October 2004 05:36 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
You will want to make yourself familiar with the DBMS_LOB package.
----------------------------------------------------------------------
SQL> CREATE TABLE t (c CLOB);
 
Table created.
 
SQL> INSERT INTO t VALUES ('hello, this is my clob and my id number is [[12345678]].'
  2                        || CHR(10)
  3                        || 'And I hope someone can help me out :-)...and this '
  4                        || 'is the end of my CLOB.');
SQL> INSERT INTO t VALUES ('This CLOB has two sets of brackets [[first]], '
  2                        || 'but will only return the first [[second]].');
SQL> SELECT DBMS_LOB.INSTR(t.c,'[[',1,1) open_bracket_pos
  2  ,      DBMS_LOB.INSTR(t.c,']]',1,1) close_brckt_pos
  3  FROM   t
  4  /
 
OPEN_BRACKET_POS CLOSE_BRCKT_POS
---------------- ---------------
              44              53
              36              42
 
SQL> COLUMN within_brackets FORMAT A15
SQL> SELECT DBMS_LOB.SUBSTR(t2.c
  2         ,               t2.close_brckt_pos - t2.open_bracket_pos - 1
  3         ,               t2.open_bracket_pos + 1) within_brackets
  4  FROM  (SELECT t.c
  5         ,      DBMS_LOB.INSTR(t.c,'[[',1,1) open_bracket_pos
  6         ,      DBMS_LOB.INSTR(t.c,']]',1,1) close_brckt_pos
  7         FROM   t) t2
  8  /
 
WITHIN_BRACKETS
---------------
12345678
first
 
SQL>
----------------------------------------------------------------------
A caveat: you will notice that the order of the numeric parameters to the DBMS_LOB.SUBSTR procedure (length followed by starting position) is backwards to that of the STANDARD package's SUBSTR procedure (starting position followed by length).

Report message to a moderator

Re: Search string in a CLOB [message #48150 is a reply to message #48149] Fri, 22 October 2004 12:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
As of 9i, you can just use SUBSTR and INSTR on CLOB columns, instead of DBMS_LOB.SUBSTR and DBMS_LOB.INSTR, as demonstrated below:

scott@ORA92> CREATE TABLE t (c CLOB);

Table created.

scott@ORA92> INSERT INTO t VALUES ('hello, this is my clob and my id number is [12345678].'
  2  			   || CHR(10)
  3  			   || 'And I hope someone can help me out :-)...and this '
  4  			   || 'is the end of my CLOB.');

1 row created.

scott@ORA92> INSERT INTO t VALUES ('This CLOB has two sets of brackets [first], '
  2  			   || 'but will only return the first [second].');

1 row created.

scott@ORA92> SELECT INSTR(t.c,'[',1,1) open_bracket_pos
  2  ,	    INSTR(t.c,']',1,1) close_brckt_pos
  3  FROM   t
  4  /

OPEN_BRACKET_POS CLOSE_BRCKT_POS
---------------- ---------------
              44              53
              36              42

scott@ORA92> COLUMN within_brackets FORMAT A15
scott@ORA92> SELECT SUBSTR(t2.c
  2  	    ,		    t2.open_bracket_pos + 1
  3  	    ,		    t2.close_brckt_pos - t2.open_bracket_pos - 1) within_brackets
  4  FROM  (SELECT t.c
  5  	    ,	   INSTR(t.c,'[',1,1) open_bracket_pos
  6  	    ,	   INSTR(t.c,']',1,1) close_brckt_pos
  7  	    FROM   t) t2
  8  /

WITHIN_BRACKETS
---------------
12345678
first

scott@ORA92>

Report message to a moderator

Re: Search string in a CLOB [message #48162 is a reply to message #48150] Sun, 24 October 2004 22:30 Go to previous messageGo to next message
neema
Messages: 20
Registered: March 2004
Junior Member
whn i executed this
i got the following error

SQL> SELECT INSTR(t.c,'[[',1,1) open_bracket_pos
2 , INSTR(t.c,']]',1,1) close_brckt_pos
3 FROM t
4 /
, INSTR(t.c,']]',1,1) close_brckt_pos
*
ERROR at line 2:
ORA-00932: inconsistent datatypes

but this got worked successfully , im using oracle 8i

SQL> SELECT DBMS_LOB.INSTR(t.c,'[[',1,1) open_bracket_pos
2 , DBMS_LOB.INSTR(t.c,']]',1,1) close_brckt_pos
3 FROM t
4 /

OPEN_BRACKET_POS CLOSE_BRCKT_POS
---------------- ---------------
44 53
36 42

y dint instr work and dbms_lob.instr got worked?is it bcoz clob is a lob type?

Report message to a moderator

Re: Search string in a CLOB [message #48183 is a reply to message #48162] Mon, 25 October 2004 23:06 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
As I said, as of Oracle 9i, you can use substr and instr on clobs. In Oracle 8i, you have to use dbms_lob.substr and dbms_lob.instr.

Report message to a moderator

Previous Topic: & operator in PL_SQL
Next Topic: Load data ?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jul 20 12:26:20 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.