Friday, June 5, 2009

Regexp_Instr

REGEXP_INSTR fonksiyonunun genel formatı ve bütün özelllikleri
REGEXP_INSTR(source_string, pattern_to_find)
REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]])
1. source_string is the string in which you wish to search for the pattern.
2. pattern_to_find is the pattern that you wish to search for in a string.
3. position indicates where to start searching in source_string.
4. occurrence indicates which occurrence of the pattern_to_find (in the source_string) you wish to search for.
5. For example, which occurrence of "si" do you want to extract from the source string "Mississippi".
6. return_option can be 0 or 1.
7. If return_option is 0, Oracle returns the first character of the occurrence (this is the default);
8. if return_option is 1, Oracle returns the position of the character following the occurrence.
9. match_parameter allows you to further customize your search.
10. "i" in match_parameter harf kipine duyarsız işlemlerde kullanılır
11. "c" in match_parameter harf kipine duyarlı işlemlerde kullanılır
12. "n" in match_parameter Allow match-any-character operator
13. "m" in match_parameter Treat source string as multiple line
SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual;

REGEXP_INSTR('MISSISSIPPI','SI',1,2,0,'I')
------------------------------------------
7

SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual;
REGEXP_INSTR('MISSISSIPPI','SI',1,2,0,'I')
------------------------------------------
7
SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,1,'i') FROM dual;
REGEXP_INSTR('MISSISSIPPI','SI',1,2,1,'I')
------------------------------------------
9
SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,1,0,'i') FROM dual;
REGEXP_INSTR('MISSISSIPPI','SI',1,1,0,'I')
------------------------------------------
4
SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,1,1,'i') FROM dual;
REGEXP_INSTR('MISSISSIPPI','SI',1,1,1,'I')
------------------------------------------
6
SQL> SELECT REGEXP_INSTR('This is a test for print a','a',1,2) position from dual;
POSITION
----------
26
SQL> SELECT REGEXP_INSTR('This is a test for print a','a',1,1) position from dual;
POSITION
----------
9
SQL> SELECT REGEXP_INSTR('This iaas a tesat fosr print a','a',1,3) position from dual;
POSITION
----------
11
SQL> SELECT REGEXP_INSTR('aaaThis iaas a tesat fosr print a','a',5,2) position from dual;
POSITION
----------
11
SQL> SELECT REGEXP_INSTR('aaaaaaThis iaas a tesat fosr print a','a',5,2) position from dual;
POSITION
----------
6
SQL> SELECT REGEXP_INSTR('aaaabaThis iaas a tesat fosr print a','a',5,2) position from dual;
POSITION
----------
13
SQL> SELECT REGEXP_INSTR('12345a','a',1,1,0) position FROM dual;
POSITION
----------
6
SQL> SELECT REGEXP_INSTR('12345a','a',1,1,1) position FROM dual;
POSITION
----------
7
http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm

No comments: