Oracle/PLSQL: Instr Function |INSTR functions |Case sensitive search

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

The oracle INSTR function returns the location of a sub string in a string.The search is case sensitive.If the specified string is not found, then the instr Oracle function will return 0.

syntax :

instr( string1, string2 [, start_position [, nth_appearance ] ] )

Where,
string1 is the string to search.
string2 is the substring to search for in string1.

start_position is an optional parameter (defaults to 1-first position in the string is 1) ,which is the position in string1 where the search will start.
If the start_position is negative value, the INSTR function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.(Example :4)

nth_appearance is an optional parameter(defualts to 1) the nth appearance of string2.

Examples:
1.Case sensitive search examples

SQL> select instr2('PL/SQLWorld tutorials', 'L',1,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
2
SQL>  select instr2('PL/SQLWorld tutorials', lower('L'),1,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
10
SQL> select instr2('PL/SQLWorld tutorials', upper('l'),1,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
   2

SQL>  select instr2('PL/SQLWorld tutorials', 'l',1,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
10

2.Start Position examples

SQL>  select instr2('PL/SQLWorld tutorials', 'l',1,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
10
SQL> select instr2('PL/SQLWorld tutorials', 'l',11,1) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
20

3. nth_appearance (Example indicates second appearence)

SQL> select instr2('PL/SQLWorld tutorials', 'l',1,2) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
20

4.Negative start_position examples

SQL>  select instr2('PL/SQLWorld tutorials', 'l',-3) from dual;
INSTR2('PL/SQLWORLDTUTORIALS',
10
SQL>

Other Notes :
The functions vary in how they determine the position of the substring to return.
INSTR calculates lengths using characters as defined by the input character set.
INSTRB calculates lengths using bytes.
INSTRC calculates lengths using Unicode complete characters.
INSTR2 calculates lengths using UCS2 code points.
INSTR4 calculates lengths using UCS4 code points.

Technorati Tags:
, , , , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

1 thought on “Oracle/PLSQL: Instr Function |INSTR functions |Case sensitive search

  1. Pingback: Oracle SUBSTR functions | Oracle/PLSQL: Substr() Function | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page