Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

how to get position of character in oracle sql

Oracle PL/SQL INSTR/InString Function

by

Oracle INSTRING FunctionOracle INSTRING Function is used to get the location of a sub-string in any string. SQL INSTRING Function is also used to get the Nth appearance of a sub-string within a string. PLSQL INSTR Function syntax and elaborate examples are mentioned below.

Oracle INSTR Function Syntax

SELECT INSTR(string1, string2 [, start_position][, nth_appearance])
FROM table_name;

In the above Oracle INSTRING Function Syntax,

  • string1 is the string to be searched
  • string2 is the substring to be searched in string1
  • start_position is the position in the string1 from where the string2 will be searched, it’s an optional parameter, if omitted, the default value is 1, hence search for string2 in string1 will start from 1st alphabet.
  • Nth_appearance is the nth appearance of string2, this is an optional parameter, if omitted the default value is 1.

If string2 is not found in string1 the Oracle SQL INSTRING Function returns 0.

Oracle INSTR FUNCTION – Simple Example

A simple example of SQL INSTRING Function can be to get the location of an alphabet/character in a string.

For example, the SQL INSTR Function query below returns the 1st location of the alphabet ‘e’ in ‘tech honey’.

SELECT INSTR('tech honey','e') "PLSQL INSTR"
FROM dual;

Above SQL INSTRING example returns ‘2’ as ‘e’ appears at the 2nd position for the 1st time.

Note: We have aliased INSTR(‘tech honey’,’e’) as PLSQL INSTR.

Oracle INSTR FUNCTION – Nth Appearance Example

Oracle INSTRING Function can also return the Nth appearance of a character in a string.

For example, the SQL INSTR Function query below returns the 2nd appearance of ‘e’ in ‘tech honey’.

SELECT INSTR('Tech Honey','e',1,2) "PLSQL INSTR"
FROM dual;

Above SQL INSTRING example return ‘9’ as ‘e’ appears at the 9th position for the 2nd time.

Note: We have aliased INSTR(‘Tech Honey’,’e’,1,2) as PLSQL INSTR.

Oracle INSTR FUNCTION – Location of String Example

SQL INSTR Function can also return the location of appearance of a string within a string.

For example, the SQL INSTR Function query below returns the location of appearance of ‘ech’ in ‘Tech Honey is a technical website’.

SELECT INSTR('Tech Honey is a technical website','ech') "PLSQL INSTR"
FROM dual;

Above SQL INSTR example return ‘2’ as ‘ech’ appears at the 2nd position for the 1st time.

Note: We have aliased INSTR(‘Tech Honey is a technical website’,’ech’) as PLSQL INSTR.

Oracle INSTR FUNCTION – Location Of Nth Appearance of String Example

SQL INSTR Function can also return the location of Nth appearance of a string within a string.

For example, the SQL INSTR Function query below returns the location of 2nd appearance of ‘ech’ in ‘Tech Honey is a technical website’.

SELECT INSTR('Tech Honey is a technical website','ech') "Oracle INSTRING"
FROM dual;

Above SQL INSTR example return ‘18’ as ‘ech’ appears at the 18th position for the 2nd time.

Note: We have aliased INSTR(‘Tech Honey is a technical website’,’ech’) as Oracle INSTRING