Slicing a string till nth instance of a particular substring in Oracle
How to slice a string up until nth instance of a particular substring?
Say you have a string 'ABC_DEF_ABC_DEF', and you want to get the string till second instance of 'ABC' i.e. 'ABC_DEF', then proceed as follows:
SELECT
SUBSTR('ABC_DEF_ABC_DEF', 0, INSTR('ABC_DEF_ABC_DEF'), 'ABC', 1, 2))
myString
from dual;
Output: 'ABC_DEF_'
- To remove the underscore in the end, reduce the index returned by `INSTR('ABC_DEF_ABC_DEF'), 'ABC', 1, 2)` by 1 i.e.
SELECT
SUBSTR('ABC_DEF_ABC_DEF', 0, INSTR(('ABC_DEF_ABC_DEF'), 'ABC', 1, 2) - 1)
myString
from dual;
Output: 'ABC_DEF'
You could just as easily replace 'ABC' in this query with a delimiter(#, comma etc.) to suit your needs.
DISSECTING THE QUERY:
- substr(), the substring function, is a string function that slices the original string as required e.g. from character at index 1 to character at index 5 of a string consisting of 10 characters.
The first argument('ABC_DEF_ABC_DEF') to this function is the original string.
The second argument(0) to the substr(), is the starting index of character in the original string that you want your new string to start with. Note that 0 and 1 both denote the starting character in an Oracle string.
The third argument is the number of characters to include after the starting index specified in the first argument.
So substr('Hello', 2, 3) would return 'ell'. - instr(), the in string function, returns the index of the string to identify(second argument: 'ABC'), in a given string(first argument: 'ABC_DEF_ABC_DEF').
The third argument(1) in the instr() is a nonzero integer indicating where in the original string(specified in the first argument) the search begins.
The fourth argument is an integer indicating which occurrence of string the function should search for. The default values of occurrence is1, meaning the function searches for the first occurrence of substring. - So the above mentioned select query returns that substring of the original string('ABC_DEF_ABC_DEF'), which begins from the first character of this string and ends at the index returned by the instr() i.e. the index of the second occurrence of the string 'ABC' in 'ABC_DEF_ABC_DEF'.