Learn how to find the position of a sub string from the main string with oracle INSTR function with example queries.
INSTR –tells the position of sub-string from the main string.
IN refers to Index of sub string
STR refers the Main string
INSTR() is a function that search for the position of substring in the main string and returns the position value of substring.
Below is the syntax of the Oracle INSTR() function
Syntax
INSTR(string , substring [, start_position [, occurrence]])
string – is the string or character expression that contains the sub-string to be found.
sub-string – is the string to be searched in the main string.
start_position- specifies the position that sub-string to be searched from the main string.
value of start_position can be either negative or positive but not zero (a non zero integer value).
The start_position is an optional parameter. The default value of the start_position is 1.
It means that, by default, the INSTR() function searches from the beginning of the string.
Positive value indicates that search from left(begin) to right(end).
Negative value indicates that search from right(end) to left(begin).
Occurrence- is an positive integer that specifies which occurrence of the sub-string for which the INSTR() function should search. The occurrence is optional and its default value is 1, meaning that the INSTR() function searches for the first occurrence of the sub-string by default.
Example queries for finding the position of a sub-string
INPUT STRING:
[email protected]
QUERY 1:
find the index of first occurrence and second of occurrence of “.” from the input string.
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '.', 1, 1) firstDOT_POSITION,
INSTR(EMAIL_ID, '.', 1, 2) SecondDot_Position
FROM CTE;| FIRSTDOT_POSITION | SECONDDOT_POSITION |
| 10 | 18 |
QUERY 2:
find the index of @ for the give input string.
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '@', 1, 1) firstAT
FROM CTE;
| FIRSTAT |
| 20 |
QUERY 3:
find the index last occurrence of “.” for the give input string.
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '.', -1, 1) LASTDOT_POSITION
FROM CTE;| LASTDOT_POSITION |
| 26 |
Below 3 queries gives same result when you run.
QUERY 1: WITHOUT start_position,occurrence
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '.') firstDOT_POSITION
FROM CTE;//IS SIMILAR TO
QUERY 2: only with start_position
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '.',1) firstDOT_POSITION
FROM CTE;//IS SIMILAR TO
QUERY 3: both with start_position and occurrence
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, '.',1,1) firstDOT_POSITION
FROM CTE;| FIRSTDOT_POSITION |
| 10 |
Query example to Search for a sub-string that does not exist in a string
WITH CTE(EMAIL_ID) AS
(SELECT '[email protected]' AS EMAIL_ID
FROM DUAL
)
SELECT INSTR(EMAIL_ID, 'E') substring_location
FROM CTE;| substring_location |
| 0 |