Menu
I need to find the last index of a string (e.g. -) within another string (e.g. JD-EQ-0001 in Oracle's SQL (version 8i). Is there a way to do this with INSTR() or another function?
463
Description. The Oracle INSTR function is used to search string for substring and find the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring.
The INSTR functions search string for substring . The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set.
INSTR(PHONE, '-') gives the index of - in the PHONE column, in your case 4. and then SUBSTR(PHONE, 1, 4 - 1) or SUBSTR(PHONE, 1, 3) gives the substring of the PHONE column from the 1st that has length of 3 chars which is 362 , if the value PHONE column is 362-127-4285 .
The INSTR function searches a character string for a specified substring, and returns the character position in that string where an occurrence of that a substring ends, based on a count of substring occurrences.
Use -1 as the start position:
INSTR('JD-EQ-0001', '-', -1) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
