Oracle INSTR Function - SQL Syntax Examples

By M&S Consulting (Ashok) on May 26th, 2009
Filed Under Technical Tips // Tags:

The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming languages.

Below shows the INSTR function along with the arguments it takes:

1
instr(string, substring)
1
instr(string, substring, start_position)
1
instr(string, substring, start_position, occurrence)

The Oracle INSTR function returns the position (an integer) within string of the first character in substring that was found while using the corresponding start_position and occurrence.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

  1. The first character of string is at start_position 1. start_position is defaulted as 1. If start_position is set to 0, 0 will always be returned, and thus, is not a useful value. If start_position is negative, searching for substring will begin at start_position characters counted from the end (right) of string and searching will be conducted towards the start (right-to-left) of string.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    SELECT instr('abcdabcd','b') FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',1) FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',4) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',0) FROM dual
    --returns: 0
     
    SELECT instr('abcdabcd','b',-1) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',-4) FROM dual
    --returns: 2
  2. substring may exist within string more than once. The ocurrence attribute is a positive integer that allows you specify which occurrence you are searching for. Remember, if you would like to find the 2cnd-to-last occurrence of substring within string, you should search using a start_position of -1 to indicate that searching should be conducted backwards, leaving occurrence at a value of 2.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    SELECT instr('abcdabcd','b',1,1) FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',1,2) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',1,0) FROM dual
    --returns: ORA-01428: argument '0' is out of range
     
    SELECT instr('abcdabcd','b',1,-1) FROM dual
    --returns: ORA-01428: argument '-1' is out of range
     
    SELECT instr('abcdabcd','b',-1,1) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',-1,2) FROM dual
    --returns: 2

string and substringcan be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both start_position and ocurrence must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer (remember that start_position should not be 0 and ocurrence should be greater than or equal to 1). The return value is a NUMBER, and will return 0 when nothing is found in the search.

INSTR is most powerful and often used in practice with the Oracle SUBSTR SQL function. Here are some examples of how to combine SUBSTR with INSTR.

Bookmark and Share

Related Information:

  1. Oracle SUBSTR Function - SQL Syntax Examples
    The Oracle SUBSTR SQL Function is very common, and many times misused. Below shows the SUBSTR function along with the...
  2. Oracle SUBSTR with INTSR Function - SQL Syntax Examples
    Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...
  3. Oracle TO_DATE Function - SQL Syntax Examples
    The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...
  4. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)
    This article provides common examples of TO_CHAR using dates....
  5. SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples
    The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text. LIKE provides a...
  6. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX
    Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...
  7. Google Custom Search Engine (CSE)
    The site search game is changing with Google CSE....
  8. Oracle SQL Subselect Statements
    It is becoming more common that I find myself surprised by self-proclaimed, seasoned SQL developers who do not even understand...

Leave a Reply


Archives

Recent Comments

  • Kavitha Muniraj said: Hi, Could you please send a script which checks the status of forms and reports of oracle...
  • Siddharth said: Hey thanks a lot !! Have been lookin all over for this :) My PC may not be able t handle FlashBuilder...
  • Cyrex said: Hey man why I cant install Apex 4? is there anyone can help me.,?
  • sharanabasava said: ALTER TABLE [my_table_name] RENAME COLUMN [current_column_name] TO [new_column_name]; Sir,I...
  • Huub Vankan said: Thanks for this easy solution. Indeed strange that is does not work in the (x86) folder….

Calendar

July 2010
M T W T F S S
« Jun    
 1234
567891011
12131415161718
19202122232425
262728293031  

Featured Testimonial

"M&S has taken 'partnership' to a new level. They are not only a deeply skilled service provider, the M&S team is actually a mission critical component to our operations, taking on new initiatives as well as maintaining custom, open source, and COTS solutions."

CEO, MedPivot

Free SQL Service