SQL Query to Find Character Position in Oracle

Learn how to Find Character Position in Oracle SQL Query. Oracle database provides the powerful INSTR() function to perform this task efficiently. In this tutorial, we will learn how to use the INSTR() function to find the position of a given character in a field with a complete example and output.

  • Here, we are finding the position of a given character in a field.
  • First, we create an Employee
  • The Employee table contains three columns: ID, Name, and Address.
  • To view the data stored in the table, execute the following query: Select * from Employee;

fetch the position of a given character

Use INSTR function :-

The INSTR() function is used to search a string for a specified character or substring. It returns a numeric value indicating the position of the first occurrence of the specified substring within the string.

  • Position counting starts from 1 in Oracle.
  • If the specified substring is not found, the INSTR() function returns 0.

Query : Select INSTR(Name, ‘b’) as position from Employee;

position of a given character in field

  • In the first row, the character ‘b’ appears in the Name field “abhinavn” at position 2, so the result is 2.
  • In the second row, the character ‘b’ does not exist in the Name field “Pankaj”, so the INSTR() function returns 0.

Advantages of Using INSTR Function

  1. Easy to Find Character Position
    The INSTR() function quickly finds the position of a character or substring within a string.
  2.  Returns Numeric Position
    It returns the position of the first occurrence of the specified substring, making it easy to use in conditions and calculations.
  3. Supports Substring Search
    You can search for a single character or multiple characters within a string.
  4. Handles Missing Values Efficiently
    If the specified substring is not found, the function returns 0, helping you identify unmatched records.
  5. Supports Starting Position
    You can specify the position from which the search should begin.
    SELECT INSTR(‘DATABASE’, ‘A’, 3) FROM DUAL;
  6. Supports Multiple Occurrences
    You can find the position of a specific occurrence of a substring.
    SELECT INSTR(‘BANANA’, ‘A’, 1, 2) FROM DUAL;