Oracle INSTR function searches a string for a substring and returns the first position of the substring.
This page contains (Skip to topic)..
Oracle INSTR syntax
INSTR(string, substring [,position [,occurrence]])
Oracle INSTR returns
- The Oracle INSTR function returns 0 (zero) when the search is unsuccessful.
- It returns a non zero value if the search is successful. Basically, it returns the position index number.
Oracle INSTR input arguments or parameters
- The string takes any string on which we want to do the instr operation. It is the base string.
- Substring is a char or chars we want to search the position in the base string.
- Position dictates Oracle the index after which it should search for the substring in the base string.
- Occurrence tells Oracle which occurrence we want to find index or position for.
Oracle INSTR practical examples
--#1 SELECT INSTR('succession','s',2,2) FROM dual; --#2 SELECT INSTR('succession','s') FROM dual; --#3 SELECT INSTR('succession','s',2) FROM dual;
SUBSTR and INSTR in oracle with examples
Now that we understood the Oracle SUBSTR and Oracle INSTR function, let us see how we can use both functions at a time.
Let us say we have a date with time string i.e. ’01-05-1991 22:10′ We want to extract the date portion only. The idea is to extract a substring until the first space. So the substring will be ’01-05-1991′ i.e. just the date portion.
select '01-05-1991 22:10' from dual; --#1: get the index of first space select instr('01-05-1991 22:10', ' ') from dual; Result: 11 --#2: extract the date by locating the space select substr('01-05-1991 22:10', 0, instr('01-05-1991 22:10',' ') ) from dual; Result: 01-05-1991
The #1 point we are finding the space index in the base string. That is the input to the step #2. Hence, we are extracting the date part on the fly. The idea here is, we are not hard-coding the space position here.
select substr('1-5-1991 22:10', 0, instr('1-5-1991 22:10',' ')) from dual; Result: 1-5-1991