Does Oracle 10g supports REGEXP_LIKE function? [message #379395] |
Tue, 06 January 2009 05:35  |
 |
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 #379405 is a reply to message #379395] |
Tue, 06 January 2009 06:03   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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 #562340 is a reply to message #379403] |
Tue, 31 July 2012 08:02   |
Solomon Yakobson
Messages: 3303 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
delna.sexy wrote on Tue, 06 January 2009 06:53Is 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.
|
|
|
|
|
|