Home » SQL & PL/SQL » SQL & PL/SQL » String Functions
String Functions [message #189205] Wed, 23 August 2006 11:03 Go to next message
cacheboy
Messages: 21
Registered: August 2006
Junior Member
Is it possible to implement a contains function?

Example, I am given a varchar2 Value, I want to check if a varchar2 subvalue exists in Value.

As in I am looking for 'this is Fun'

And my Value is 'PLSQL is not that fun but this is Fun'

So my function contains will true.

The next level of difficult is to make the contains function case-insensitive.

Anyone knows how to do this in PL/SQL?
Re: String Functions [message #189209 is a reply to message #189205] Wed, 23 August 2006 11:17 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why make a function, this is already in oracle. Use

select 'OK'
from dual
where instr('PLSQL is not that fun but this is Fun','this is Fun') > 0;
Re: String Functions [message #189223 is a reply to message #189209] Wed, 23 August 2006 12:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
and to add the case-insensitivity:
select 'OK'
from dual
where instr(upper('PLSQL is not that fun but this is fun'), upper('This is Fun')) > 0;


But PL/SQL IS fun, so this query gives suspect results.

[Updated on: Wed, 23 August 2006 12:16]

Report message to a moderator

Re: String Functions [message #189224 is a reply to message #189223] Wed, 23 August 2006 12:21 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
joy_division wrote on Wed, 23 August 2006 12:15

and to add the case-insensitivity:
select 'OK'
from dual
where instr(upper('PLSQL is not that fun but this is fun'), upper('This is Fun')) > 0;


But PL/SQL IS fun, so this query gives suspect results.


Thanks for the catch, I missed the requirement that it be case-insensitive.
Re: String Functions [message #189323 is a reply to message #189205] Thu, 24 August 2006 02:53 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member

SCOTT@10gXE> CREATE TABLE your_table (your_column VARCHAR2 (60))
  2  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO your_table VALUES ('PLSQL is not that fun but this is Fun')
  3  INTO your_table VALUES ('PLSQL is not that fun but THIS IS FUN')
  4  INTO your_table VALUES ('What fun this is')
  5  INTO your_table VALUES ('Isn''t this fun?')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@10gXE> CREATE INDEX your_index ON your_table (your_column)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('STOPLIST CTXSYS.EMPTY_STOPLIST
  5  	 SYNC (ON COMMIT)')
  6  /

Index created.

SCOTT@10gXE> SELECT * FROM your_table
  2  WHERE CONTAINS (your_column, '{this is fun}') > 0
  3  /

YOUR_COLUMN
------------------------------------------------------------
PLSQL is not that fun but this is Fun
PLSQL is not that fun but THIS IS FUN

SCOTT@10gXE>




Previous Topic: Need Help in Job Creation In ORacle
Next Topic: Export table as SQL query
Goto Forum:
  


Current Time: Mon Dec 05 08:58:24 CST 2016

Total time taken to generate the page: 0.08283 seconds