Oracle Advance Function
Function Name Description
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REGEXP_LIKE
Similar to the LIKE operator, but performs
regular expression matching instead of
simple pattern matching
REGEXP_INSTR
Searches for a given string for a regular
expression pattern and returns the position
were the match is found
REGEXP_REPLACE
Searches for a regular expression pattern and
replaces it with a replacement string
REGEXP_SUBSTR
Searches for a regular expression pattern
within a given string and returns the matched
substring
REGEXP_COUNT
Searches for a regular expression pattern and
returns the count
REGEXP_LIKE
(sourcestr, pattern [,options])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
options: are as follows
'c': case sensitive search/match
'i': case insensitive search/match
'm': parses the source string as individual lines
's': parses the source string as a single line
'n': allows a period (.) wild character to match a newline
'x': instructs parser to ignore whitespace characters
Character Class Description
^ Anchor the expression to the start of a line
$ Anchor the expression to the end of a line
Equivalence Classes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
= = Oracle supports the equivalence classes through
the POSIX '[==]' syntax. A base letter and all of
its accented versions constitute an equivalence
class. For example, the equivalence class '[=a=]'
matches ä and â. The equivalence classes are valid
only inside the bracketed expression
Match Options determine if the target is treated checked for
case-sensitivity and whether or not the target is evaluated
line by line or as a continuous string.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
c Case sensitive matching
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character
Posix Characters tend to look very ugly but have the advantage
that also take into account the 'locale', that is, any variant
of the local language/coding system.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
[:digit:] Only the digits 0 to 9
[:alnum:] Any alphanumeric character 0 to 9 OR A to Z or a to z.
[:alpha:] Any alpha character A to Z or a to z.
[:blank:] Space and TAB characters only.
[:xdigit:] Hexadecimal notation 0-9, A-F, a-f.
[:punct:] Punctuation symbols
--------------------------------
% . , " ' ? ! : # $ & ( ) * ;
+ - / = @ [ ] \ ^ _ { } | ~
--------------------------------
[:print:] Any printable character.
[:space:] Any whitespace characters (space, tab, NL, FF, VT, CR).
Many system abbreviate as \s.
[:graph:] Exclude whitespace (SPACE, TAB). Many system abbreviate as \W.
[:upper:] Any alpha character A to Z.
[:lower:] Any alpha character a to z.
[:cntrl:] Control Characters NL CR LF TAB VT FF NUL SOH STX
EXT EOT ENQ ACK SO SI DLE DC1 DC2 DC3 DC4 NAK SYN
ETB CAN EM SUB ESC IS1 IS2 IS3 IS4 DEL.
Quantifier Characters control the number of times a character
or string is found in a search.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
Alternative Matching And Grouping Characters
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
| Separates alternates, often used with grouping
operator ()
( ) Groups subexpression into a unit for alternations, for
quantifiers, or for backreferencing
[char] Indicates a character list; most metacharacters inside a
character list are understood as literals, with the
exception of character classes, and the ^
and - metacharacters
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Regex Cheat Sheet (non-posix)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modifiers:
i case-insensitive pattern matching.
g global replace, or replace all
m Treat string as multiple lines. That is,
change ``^'' and ``$'' from matching at only
the very start or end of the string to the
start or end of any line anywhere within the string
s Treat string as single line. That is, change ``.'' to
match any character whatsoever, even a newline, which
it normally would not match.
x Extend your pattern's legibility by permitting
whitespace and comments.
Special Characters:
The following should be escaped if you are trying to
match that character:
\ ^ . $ | ( ) [ ]
* + ? { } ,
Special Character Definitions:
\ Quote the next metacharacter
^ Match the beginning of the line
. Match any character (except newline)
$ Match the end of the line (or before newline at the end)
| Alternation
() Grouping
[] Character class
* Match 0 or more times
+ Match 1 or more times
? Match 1 or 0 times
{n} Match exactly n times
{n,} Match at least n times
{n,m} Match at least n but not more than m times
More Special Characters:
\t tab (HT, TAB)
\n newline (LF, NL)
\r return (CR)
\f form feed (FF)
\a alarm (bell) (BEL)
\e escape (think troff) (ESC)
\033 octal char (think of a PDP-11)
\x1B hex char
\c[ control char
\l lowercase next char (think vi)
\u uppercase next char (think vi)
\L lowercase till \E (think vi)
\U uppercase till \E (think vi)
\E end case modification (think vi)
\Q quote (disable) pattern metacharacters till \E
\w Match a "word" character (alphanumeric plus "_")
\W Match a non-word character
\s Match a whitespace character
\S Match a non-whitespace character
\d Match a digit character
\D Match a non-digit character
\b Match a word boundary
\B Match a non-(word boundary)
\A Match only at beginning of string
\Z Match only at end of string, or before newline at the end
\z Match only at end of string
\G Match only where previous m//g left off (works only with /g)
SQL> select regexp_instr('WBBCDFFGT','B+C') from dual;
REGEXP_INSTR('WBBCDFFGT','B+C')
-------------------------------
2
Where one and more occurrence of b followed by C
select regexp_instr('WBBCDFFGT','B.C') cc from dual;
SQL> select regexp_instr('WBBCDFFGET bbc','(b+c)$') from dual;
REGEXP_INSTR('WBBCDFFGETBBC','(B+C)$')
--------------------------------------
12
if yes it occurrs at the end of line
SQL> select regexp_instr('AAAAAAAAAAAABBCFFGGR','B+C(G|F){4}') from dual;
REGEXP_INSTR('AAAAAAAAAAAABBCFFGGR','B+C(G|F){4}')
--------------------------------------------------
13
One ore more occurrencees of B followed by C follwed by four Occurrence og G or F begin
select regexp_instr('ABBCD HJHF SDFF GBBCFFGGR','[$F]') from dual;
SQL> select regexp_instr('ABBCD HJHF SDFF GBBCFFGGR','[$F]') from dual;
REGEXP_INSTR('ABBCDHJHFSDFFGBBCFFGGR','[$F]')
---------------------------------------------
10
SQL> select regexp_instr('ABBCD H123 1234B BCFF GBBCFFGGR','[[:digit:]]{4}') from dual ;
REGEXP_INSTR('ABBCDH1231234BBCFFGBBCFFGGR','[[:DIGIT:]]{4}')
------------------------------------------------------------
12
SELECT park_name,
SUBSTR(
description,
REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,0,'i'), -- start
REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,1,'i') -- minus
REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,0,'i') -- nbr
) acres
FROM michigan_park
WHERE
REGEXP_LIKE(description, '[^ ]+ acres|[^ ]+-acre','i');
SELECT table_name,
index_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE table_name = 'LOCATION_CODE' ;
Function Name Description
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REGEXP_LIKE
Similar to the LIKE operator, but performs
regular expression matching instead of
simple pattern matching
REGEXP_INSTR
Searches for a given string for a regular
expression pattern and returns the position
were the match is found
REGEXP_REPLACE
Searches for a regular expression pattern and
replaces it with a replacement string
REGEXP_SUBSTR
Searches for a regular expression pattern
within a given string and returns the matched
substring
REGEXP_COUNT
Searches for a regular expression pattern and
returns the count
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
options: are as follows
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
'c': Use case-sensitive matching (default)
'i': Use case-insensitive matching
'n': Allow match-any-character operator
'm': Treat source string as multiple line
'c': case sensitive search/match
'i': case insensitive search/match
'm': parses the source string as individual lines
's': parses the source string as a single line
'n': allows a period (.) wild character to match a newline
'x': instructs parser to ignore whitespace characters
Character Class Description
^ Anchor the expression to the start of a line
$ Anchor the expression to the end of a line
Equivalence Classes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
= = Oracle supports the equivalence classes through
the POSIX '[==]' syntax. A base letter and all of
its accented versions constitute an equivalence
class. For example, the equivalence class '[=a=]'
matches ä and â. The equivalence classes are valid
only inside the bracketed expression
Match Options determine if the target is treated checked for
case-sensitivity and whether or not the target is evaluated
line by line or as a continuous string.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
c Case sensitive matching
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character
Posix Characters tend to look very ugly but have the advantage
that also take into account the 'locale', that is, any variant
of the local language/coding system.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
[:digit:] Only the digits 0 to 9
[:alnum:] Any alphanumeric character 0 to 9 OR A to Z or a to z.
[:alpha:] Any alpha character A to Z or a to z.
[:blank:] Space and TAB characters only.
[:xdigit:] Hexadecimal notation 0-9, A-F, a-f.
[:punct:] Punctuation symbols
--------------------------------
% . , " ' ? ! : # $ & ( ) * ;
+ - / = @ [ ] \ ^ _ { } | ~
--------------------------------
[:print:] Any printable character.
[:space:] Any whitespace characters (space, tab, NL, FF, VT, CR).
Many system abbreviate as \s.
[:graph:] Exclude whitespace (SPACE, TAB). Many system abbreviate as \W.
[:upper:] Any alpha character A to Z.
[:lower:] Any alpha character a to z.
[:cntrl:] Control Characters NL CR LF TAB VT FF NUL SOH STX
EXT EOT ENQ ACK SO SI DLE DC1 DC2 DC3 DC4 NAK SYN
ETB CAN EM SUB ESC IS1 IS2 IS3 IS4 DEL.
Quantifier Characters control the number of times a character
or string is found in a search.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
Alternative Matching And Grouping Characters
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
| Separates alternates, often used with grouping
operator ()
( ) Groups subexpression into a unit for alternations, for
quantifiers, or for backreferencing
[char] Indicates a character list; most metacharacters inside a
character list are understood as literals, with the
exception of character classes, and the ^
and - metacharacters
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Regex Cheat Sheet (non-posix)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modifiers:
i case-insensitive pattern matching.
g global replace, or replace all
m Treat string as multiple lines. That is,
change ``^'' and ``$'' from matching at only
the very start or end of the string to the
start or end of any line anywhere within the string
s Treat string as single line. That is, change ``.'' to
match any character whatsoever, even a newline, which
it normally would not match.
x Extend your pattern's legibility by permitting
whitespace and comments.
Special Characters:
The following should be escaped if you are trying to
match that character:
\ ^ . $ | ( ) [ ]
* + ? { } ,
Special Character Definitions:
\ Quote the next metacharacter
^ Match the beginning of the line
. Match any character (except newline)
$ Match the end of the line (or before newline at the end)
| Alternation
() Grouping
[] Character class
* Match 0 or more times
+ Match 1 or more times
? Match 1 or 0 times
{n} Match exactly n times
{n,} Match at least n times
{n,m} Match at least n but not more than m times
More Special Characters:
\t tab (HT, TAB)
\n newline (LF, NL)
\r return (CR)
\f form feed (FF)
\a alarm (bell) (BEL)
\e escape (think troff) (ESC)
\033 octal char (think of a PDP-11)
\x1B hex char
\c[ control char
\l lowercase next char (think vi)
\u uppercase next char (think vi)
\L lowercase till \E (think vi)
\U uppercase till \E (think vi)
\E end case modification (think vi)
\Q quote (disable) pattern metacharacters till \E
Even More Special Characters:
\w Match a "word" character (alphanumeric plus "_")
\W Match a non-word character
\s Match a whitespace character
\S Match a non-whitespace character
\d Match a digit character
\D Match a non-digit character
\b Match a word boundary
\B Match a non-(word boundary)
\A Match only at beginning of string
\Z Match only at end of string, or before newline at the end
\z Match only at end of string
\G Match only where previous m//g left off (works only with /g)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Labels: Sql Plus/ PLSQL
Posted by Sänjay at 6/15/2009
regexp_instr (string, pattern, position, occurence, return-option, parameters)
regexp_instr (
string,
pattern,
position,
occurrence,
return-option,
parameters)
parameters can be a combination of
* i: to match case insensitively
* c: to match case sensitively
* n: to make the dot (.) match new lines as well
* m: to make ^ and $ match beginning and end of a line in a multiline string
* x: to ignore white spaces.
* match 0 or more times
+ match 1 or more times
? match 0 or 1 time
^ start of the expression
$ end of the the expression
{m} match exactly m times
{m,} match at least m times
{m, n} match at least m times but no more than n times
| alternate operator
( ) group used for subexpression and backreferencing
[char] character List (can be used as literals except ^ ,- and character class)
\digit backslash followed by a digit between 1 and 9 ,used for backrerencing the respective subexpression.
[:alpha:] alphabetic characters
[:lower:] lowercase alphabetic characters
[:upper:] uppercase alphabetic characters
[:digit:] numeric digits
[:alnum:] alphanumeric characters
[:space:] nonprinting space characters e.g. carriage return, newline, vertical tab, and form feed
[:punct:] punctuation characters
[:cntrl:] nonprinting control characters
[:print:] printable characters
regexp_instr is an Oracle SQL function that enables regular expressions in queries.
It enhances the 'traditional' instr.
create table ADDRESSES(addr varchar2(50));
insert into ADDRESSES
values ('123 4TH sT.');
insert into ADDRESSES
values ('4 mAPLE $T.');
insert into ADDRESSES
values ('2167 GREENBRIER bLVD.');
insert into ADDRESSES
values ('33 tHIRD sT.');
insert into ADDRESSES
values ('oNE FIRST dRIVE');
insert into ADDRESSES
values ('1664 1/2 sPRINGHILL aVE');
insert into ADDRESSES
values ('2003 GEAUX iLLINI dR.');
select regexp_instr('Mark has a cold','a') position from dual;
SQL> select regexp_instr('Mark has a cold','a') position from dual;
REGEXP_INSTR('MARKHASACOLD','A')
--------------------------------
2
select regexp_instr('Mark has a cold','a',1) position from dual;
select regexp_instr('Mark has a cold','a',1,3) position from dual;
select regexp_instr('qqqqaa','a') position from dual;
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$')
AS rx_instr FROM dual
select REGEXP_INSTR('Mary has a cold','a',3) position FROM dual
select regexp_instr('Mark has a cold','a',1,2) position from dual;
REGEXP_INSTR('MARKHASACOLD','A',1,2)
------------------------------------
7
select regexp_instr('Mark has a cold','a',1,1) position from dual;
REGEXP_INSTR('MARKHASACOLD','A',1,1)
------------------------------------
2
select regexp_instr('Mark has a cold','a',1,3) position from dual;
REGEXP_INSTR('MARKHASACOLD','A',1,3)
------------------------------------
10
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL;
SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA','[ ]+', 1, 6)
"REGEXP_INSTR" FROM DUAL;
* '^' for the beginning of a string
* '$' for the end of string
* '.' for any character
* Ranges of characters, such as '[a-z]', for any ASCII lowercase letter,
which is equivalent to the character class: "[[:lower:]]""
* '?' allows a preceding character to match zero or one time
* '+' allows a preceding character to match one or more times
* '*' for zero or more times
SQL> select regexp_instr('AAAAAAAAAAAABBCFFGGR','B+C(G|F){4}') from dual;
REGEXP_INSTR('AAAAAAAAAAAABBCFFGGR','B+C(G|F){4}')
--------------------------------------------------
13
One ore more occurrencees of B followed by C follwed by four Occurrence og G or F begin
select regexp_instr('ABBCD HJHF SDFF GBBCFFGGR','[$F]') from dual;
SQL> select regexp_instr('ABBCD HJHF SDFF GBBCFFGGR','[$F]') from dual;
REGEXP_INSTR('ABBCDHJHFSDFFGBBCFFGGR','[$F]')
---------------------------------------------
10
SQL> select regexp_instr('ABBCD H123 1234B BCFF GBBCFFGGR','[[:digit:]]{4}') from dual ;
REGEXP_INSTR('ABBCDH1231234BBCFFGBBCFFGGR','[[:DIGIT:]]{4}')
------------------------------------------------------------
12
select regexp_instr('ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[^ ]+',1,2) from dual;
select regexp_instr('ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[ ]+',1,2) from dual;
select regexp_instr('ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[ ]+',1,1) from dual;
select regexp_instr('ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[ ]',1,1) from dual;
select regexp_instr(' ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[ ]',1,1) from dual;
select regexp_instr('ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[ ]',1,1) from dual;
SQL> select regexp_instr(' ABBCD HJHF SDFFB , BCFF GBBCFFGR, AA','[^ ]',1,1) from dual;
REGEXP_INSTR('ABBCDHJHFSDFFB,BCFFGBBCFFGR,AA','[^]',1,1)
--------------------------------------------------------
3
first Non black value
SQL> select regexp_instr('BBBBBBBCC','[^B]') from dual;
REGEXP_INSTR('BBBBBBBCC','[^B]')
--------------------------------
8
first non b char
SQL> select regexp_instr('ABBBRD HJHF ODFFB, BCFF RBBCFFGR, AA','[r|o][[:alpha:]]{4}',2,2,1,'i') from dual ;
REGEXP_INSTR('ABBBRDHJHFODFFB,BCFFRBBCFFGR,AA','[R|O][[:ALPHA:]]{4}',2,2,1,'I')
-------------------------------------------------------------------------------
30
create table history ( empno NUMBER(4)
, beginyear NUMBER(4)
, begindate DATE
, enddate DATE
, deptno NUMBER(2)
, msal NUMBER(6,2)
, comments VARCHAR2(60)
) ;
insert into history values (9,2000,date '2000-01-01',date '2002-01-02',40, 950,'history for 9');
insert into history values (8,2000,date '2000-01-02', NULL ,20, 800,'');
insert into history values (7,1988,date '2000-01-06',date '2002-01-07',30,1000,'');
insert into history values (6,1989,date '2000-01-07',date '2002-01-12',30,1300,'');
insert into history values (5,1993,date '2000-01-12',date '2002-01-10',30,1500,'history for 5');
insert into history values (4,1995,date '2000-01-10',date '2002-01-11',30,1700,'');
insert into history values (3,1999,date '2000-01-11', NULL ,30,1600,'');
insert into history values (2,1986,date '2000-01-10',date '2002-01-08',20,1000,'history for 2');
insert into history values (1,1987,date '2000-01-08',date '2002-01-01',30,1000,'history for 1');
insert into history values (7,1989,date '2000-01-01',date '2002-05-12',30,1150,'history for 7');
select comments from history where regexp_instr(comments, '[^ ]+', 1, 9) > 0;
SELECT REGEXP_SUBSTR('This is a sentence.','.*[^.!:]') FROM dual;
from esearch.ent_search_update_table
ESEARCH.ENT_SEARCH_UPDATE_VIEW
WITH t AS (SELECT 'abc' col1
FROM dual
UNION
SELECT '123'
FROM dual
UNION
SELECT 'a2'
FROM dual
UNION
SELECT '1b'
FROM dual
UNION
SELECT '!'
FROM dual
)
SELECT t.col1
, REGEXP_SUBSTR(t.col1, '[[:alpha:]][[:digit:]]|[[:digit:]][[:alpha:]]') check_cond
FROM t
;
col EX1 format a10
col EX2 format a10
with t as (
select 'ABCDEFG' col1 from dual union
select '123456ABCDEFG' col1 from dual union
select 'ZZZ123456ABCDEFG' col1 from dual union
select 'GFEDCBA123456' col1 from dual)
-- end of sample data
select regexp_replace(col1, '[[:digit:]]') ex1,
regexp_replace(col1, '\d') ex2
SQL> SELECT REGEXP_INSTR ('BCDEFG123456',
'[[:alpha:]]',
1,
1,
0,
'i'
) alpha_pos
FROM DUAL;
ALPHA_POS
----------
1
SQL> select substr (x,1,REGEXP_INSTR(x,'[[:alpha:]]')) Allpha,substr (x,REGEXP_INSTR(x,'[[:alpha:]]|[[:punct:]]')) "alphaapunct" ,x from test3
2 ;
ALLPHA
----------------------------------------
alphaapunct X
---------------------------------------- ----------
d
dd dd
d
dddd dddd
,d
,dddd ,dddd
SQL> col Allpha format a10
SQL> col alphaapunct format a10
SQL> /
SQL> create table test3 ( x varchar2(10));
Table created.
SQL> insert into test3 values (' dd');
1 row created.
SQL> insert into test3 values ('dddd');
1 row created.
SQL> commit;
SQL> insert into test3 values (' ,dddd');
1 row created.
SQL> commit;
Commit complete.
ALLPHA alphaapunc X
---------- ---------- ----------
d dd dd
d dddd dddd
,d ,dddd ,dddd
SQL> select * from test3;
X
----------
dd
dddd
,dddd
SQL> insert into test3 values (' ggg^M');
1 row created.
SQL> commit;
Commit complete.
SQL> select REGEXP_INSTR(x,'[[:cntrl:]]') from test3
2 /
REGEXP_INSTR(X,'[[:CNTRL:]]')
-----------------------------
0
0
0
5
SQL> --[:cntrl:] matches control characters.
SQL> SELECT description
2 FROM testTable
3 WHERE NOT REGEXP_LIKE(description,'[:cntrl]');
SQL> insert into test3 values ('ggg^M');
1 row created.
this is control vm
select REGEXP_INSTR(x,'[[:cntrl:]]+') from test3;
REGEXP_INSTR(X,'[[:CNTRL:]]+')
------------------------------
0
0
0
5
0
0
0
4
8 rows selected.
select REGEXP_INSTR(x,'[[:cntrl:]]+') from test3;
REGEXP_INSTR(X,'[[:CNTRL:]]+')
------------------------------
0
0
0
5
0
0
0
4
8 rows selected.
select REGEXP_INSTR(x,'[[:cntrl:]]+') from test3;
REGEXP_INSTR(X,'[[:CNTRL:]]+')
------------------------------
0
0
0
5
0
0
0
4
8 rows selected.
SQL> exec :val := 'karthick'
PL/SQL procedure successfully completed.
SQL> select decode(regexp_instr(:val,'[[:digit:]]'),0,'NO',decode(regexp_instr(:val,'[[:alpha:]]'),0,'NO','YES')) from dual
2 /
DEC
---
NO
SQL> exec :val := 'karthick123'
PL/SQL procedure successfully completed.
SQL> select decode(regexp_instr(:val,'[[:digit:]]'),0,'NO',decode(regexp_instr(:val,'[[:alpha:]]'),0,'NO','YES')) from dual
2 /
DEC
---
YES
SQL> exec :val := '123'
PL/SQL procedure successfully completed.
SQL> select decode(regexp_instr(:val,'[[:digit:]]'),0,'NO',decode(regexp_instr(:val,'[[:alpha:]]'),0,'NO','YES')) from dual
2 /
DEC
---
NO
SQL> exec :val := '1ds2d3'
PL/SQL procedure successfully completed.
SQL> select decode(regexp_instr(:val,'[[:digit:]]'),0,'NO',decode(regexp_instr(:val,'[[:alpha:]]'),0,'NO','YES')) from dual
2 /
DEC
---
YES
SQL> insert into t_product values (1,'10','Option1');
insert into t_product values (2,'20','Option2');
insert into t_product values (3,'30','Option3');
insert into t_product values (4,'40a','Option4');
insert into t_product values (5,'Z50','Option5');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> select product_code from t_product
where regexp_like(product_code, '[^[:digit:]]'); 2
PRODUCT_CO
----------
40a
Z50
SQL> select product_code from t_product
2 where regexp_like(product_code, '[[:digit:]]');
PRODUCT_CO
----------
10
20
30
40a
Z50
SQL> SELECT REGEXP_SUBSTR('Do not' || CHR(10)|| 'Brighten the corner!','^.*$',1,2,'m') FROM dual;
REGEXP_SUBSTR('DONOT
--------------------
Brighten the corner!
SQL> SELECT REGEXP_SUBSTR('123789',
'[[:digit:]]*')
FROM dual;
123789 2 3
REGEXP
------
123789
SQL> SQL> SELECT REGEXP_SUBSTR('abc123789def','[[:digit:]]*') FROM dual;
SP2-0734: unknown command beginning "123789SELE..." - rest of line ignored.
SQL> SELECT REGEXP_SUBSTR('abc123789def','[[:digit:]]*') FROM dual;
R
-
SQL> SELECT REGEXP_SUBSTR('abc123789def','[[:digit:]]') FROM dual;
R
-
1
SQL> SELECT REGEXP_SUBSTR('abc123789def','[[:digit:]]*+') FROM dual;
R
-
SQL> SELECT REGEXP_SUBSTR('abc123789def','[[:digit:]]+') FROM dual;
REGEXP
------
123789
SQL> SELECT REGEXP_SUBSTR('abc123789def','[^[:digit:]]+') FROM dual;
REG
---
abc
SQL> SELECT REGEXP_SUBSTR('abc123789def','[^[:digit:]]$+') FROM dual;
R
-
f
Using the {m,n} form, you can specify a range of occurrences you are willing to accept. The following query uses {3,5} to match from three to five digits:
SELECT REGEXP_SUBSTR(
'1234567890','[[:digit:]]{3,5}')
FROM dual;
12345
Using {m,}, you can leave the upper end of a range unbounded:
SELECT REGEXP_SUBSTR(
'1234567890','[[:digit:]]{3,}')
FROM dual;
1234567890
Examples
Following is an example of a simple case, in which the string 'Mackinac', commonly misspelled 'Mackinaw', is located within a larger string:
SELECT REGEXP_INSTR(
'Fort Mackinac was built in 1870',
'Mackina.')
FROM dual;
6
If you're interested in the ending character position, actually one past the ending position, you can specify a value of 1 for return_option, which forces you to also specify values for position and occurrence:
SELECT REGEXP_INSTR(
'Fort Mackinac was built in 1870',
'Mackina.',1,1,1)
FROM dual;
14
The occurrence parameter enables you to locate an occurrence of a pattern other than the first:
SELECT REGEXP_INSTR(
'Fort Mackinac is near Mackinaw City',
'Mackina.',1,2)
FROM dual;
23
The following example uses position to skip the first 14 characters of the search string, beginning the search at character position 15:
SELECT REGEXP_INSTR(
'Fort Mackinac is near Mackinaw City',
'Mackina.',15)
FROM dual;
^ - Beginning of String[A-Z] - A Capital Letter[0-9]{8} - Exactly 8 digit characters$ - End of String
Here is a query validating certain use cases:
WITH test_data (text_value) AS
(
SELECT 'A12345678' FROM DUAL UNION ALL
SELECT 'a12345678' FROM DUAL UNION ALL
SELECT 'A1234567' FROM DUAL UNION ALL
SELECT 'A123456789' FROM DUAL UNION ALL
SELECT '$12345678' FROM DUAL
)
SELECT td.text_value,
CASE WHEN REGEXP_LIKE(td.text_value, '^[A-Z][0-9]{8}$') THEN 'Y' ELSE 'N' END AS VALID
FROM test_data td
*******************************
Working !!
select distinct substr(name, 1, instr(name, '/',-1)) PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
) order by 1;
===========================================
not working with REGEXP_INSTR
getting error !!
REGEXP_INSTR ORA-01428: argument '-1' is out of range
select distinct substr(name, 1, REGEXP_INSTR(name, '/',-1)) PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
) order by 1;
select distinct substr(name, 1, REGEXP_INSTR(name, '/',-1)) PATH
*
ERROR at line 1:
ORA-01428: argument '-1' is out of range
reverse search REGEXP_INSTR
with REGEXP_INSTR for windows and unix
select distinct substr(name, 1, REGEXP_INSTR(name, '[^/|\]*$')-1 )PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
union all
select FILENAME from V$BLOCK_CHANGE_TRACKING
union all
Select NAME from v$flashback_database_logfile
)
order by 1;
with t as ( select 'abcab\cabc' foo from dual)
select regexp_instr(foo, '[^/|\]*$')-1 from t
/
with t as ( select 'abcabcabc' foo from dual)
select regexp_instr(foo, '[^b]*$')-1 from t
/
