Home » SQL & PL/SQL » SQL & PL/SQL » Does Oracle 10g supports REGEXP_LIKE function?
Does Oracle 10g supports REGEXP_LIKE function? [message #379395] Tue, 06 January 2009 05:35 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello everybody,

I am using REGEXP_LIKE function, but it is giving me error like this.

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
       *
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier


But in Oracle documentation it is given under heading of 10g.
Does Oracle 10g supports this function?

regards,
Delna
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #379398 is a reply to message #379395] Tue, 06 January 2009 05:40 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
REGEXP_LIKE is used after where clause.

Please check at http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/conditions018.htm

Thanks
Trivendra

[Updated on: Tue, 06 January 2009 05:43]

Report message to a moderator

Re: Does Oracle 10g supports REGEXP_LIKE function? [message #379403 is a reply to message #379398] Tue, 06 January 2009 05:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is that a rule?
Can't I use it in any other way?

regards,
Delna
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #379405 is a reply to message #379395] Tue, 06 January 2009 06:03 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi Delna,

May be this can help you to understand more

REGEXP_LIKE Function 
- Applies a LIKE function to a regular expression pattern 
- Simplified Syntax: 

REGEXP_LIKE (source string, pattern) 
- Rules: 
	o Source string specifies source data to be scanned 
	o Pattern is the regular expression to search within the source string 
	o Returns true or false indicating whether the pattern matched the data. 
	o Used primarily in the WHERE clause 

REGEXP_LIKE Example 
- Assume we have a postal code column with A9A-9A9 format for Canadian companies 
- Get all companies in Canada with a valid postal code 
SELECT company_name 
FROM company 
WHERE REGEXP_LIKE(postal_code, '^[A-Z][0-9][A-Z][ |-][0-9][A-Z][ 0-9]$'); 


Thanks
Trivendra
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #379406 is a reply to message #379403] Tue, 06 January 2009 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Tue, 06 January 2009 12:53
Is that a rule?
Can't I use it in any other way?

regards,
Delna

Did you ask the same question for LIKE?

Regards
Michel

Re: Does Oracle 10g supports REGEXP_LIKE function? [message #379423 is a reply to message #379405] Tue, 06 January 2009 07:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Trivendra for your valuable notes.
It helped me too much.

regards,
Delna
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562331 is a reply to message #379395] Tue, 31 July 2012 06:18 Go to previous messageGo to next message
ervin
Messages: 1
Registered: July 2012
Junior Member
Hi, my solution would be:
select case when regexp_like(testcol,'^ab[cd]ef$') then 1 else 0 end from test;
1=ok; 0=nok
Regards, Ervin
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562335 is a reply to message #562331] Tue, 31 July 2012 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562340 is a reply to message #379403] Tue, 31 July 2012 08:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
delna.sexy wrote on Tue, 06 January 2009 06:53
Is that a rule?
Can't I use it in any other way?


Just lookup REGEXP_LIKE declaration in package STANDARD ($ORACLE_HOME/rdbms/admin/stdspec.sql):

  -- REGEXP_LIKE --
  function REGEXP_LIKE (srcstr   VARCHAR2 CHARACTER SET ANY_CS,
                        pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,
                        modifier VARCHAR2 DEFAULT NULL)
    return BOOLEAN;
    pragma FIPSFLAG('REGEXP_LIKE', 1452);

  function REGEXP_LIKE (srcstr   CLOB CHARACTER SET ANY_CS,
                        pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,
                        modifier VARCHAR2 DEFAULT NULL)
    return BOOLEAN;
    pragma FIPSFLAG('REGEXP_LIKE', 1452);



As you can see, it returns BOOLEAN which is not a SQL type. What you can do is:

select  case
          when regexp_like(testcol,'^ab[cd]ef$') THEN 'TRUE'
          when not regexp_like(testcol,'^ab[cd]ef$') THEN 'FALSE'
          else 'NULL'
        end
  from  test
/


SY.
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562382 is a reply to message #562340] Wed, 01 August 2012 02:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps you're looking for REGEXP_INSTR?

MHE
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562383 is a reply to message #562382] Wed, 01 August 2012 02:15 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess that Delna sorted that out already (in January 2009).
Re: Does Oracle 10g supports REGEXP_LIKE function? [message #562389 is a reply to message #562383] Wed, 01 August 2012 03:44 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
See? I've been away far too long. Very Happy

MHE
Previous Topic: Executing dynamic procedure with record type
Next Topic: Complicated query
Goto Forum:
  


Current Time: Wed Dec 07 06:50:27 CST 2016

Total time taken to generate the page: 0.11613 seconds