Oracle 12C - Select String After Last Occurrence of a Character

oracle 12c - select string after last occurrence of a character

You can probably do this with complicated regular expressions. I like the following method:

select substr(str, - instr(reverse(str), '.') + 1)

Nothing like testing to see that this doesn't work when the string is at the end. Something about - 0 = 0. Here is an improvement:

select (case when str like '%.' then ''
             else substr(str, - instr(reverse(str), ';') + 1)
        end)

EDIT:

Your example works, both when I run it on my local Oracle and in SQL Fiddle.

I am running this code:

select (case when str like '%.' then ''
             else substr(str, - instr(reverse(str), '.') + 1)
        end)
from (select 'ThisSentence.ShouldBe.SplitAfterLastPeriod.Sentence' as str from dual) t

substring, after last occurrence of character?

It should be as easy as this regex:

SELECT phone_number, REGEXP_SUBSTR(phone_number, '[^.]*$')
  FROM employees;

With the end anchor $ it should get everything that is not a . character after the final .. If the last character is . then it will return NULL.

SQL Query to select a string after last delimiter

Use REGEXP_SUBSTR

select regexp_substr('Attachments:Attachments~Attachment','[^~]+$') from dual;

• [^ ] - Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
• + - Matches one or more occurrences
• $ - Matches the end of a string

Demo on db<>fiddle

How to get string after character oracle

For a string operation as simple as this, I might just use the base INSTR() and SUBSTR() functions. In the query below, we take the substring of your column beginning at two positions after the hyphen.

SELECT
    SUBSTR(col, INSTR(col, '-') + 2) AS subject
FROM yourTable

We could also use REGEXP_SUBSTR() here (see Gordon's answer), but it would be a bit more complex and the performance might not be as good as the above query.

Get 5 Characters After Last Slash

You could match until the last occurrence of a / with a capturing group and a quantifier {1,6} to match no or flaged

Note that flaged are 6 characters instead of 5 after the /

This page shows how to get the capturing group from the match using either REGEXP_SUBSTR or REGEXP_REPLACE.

^.*/([^/]{1,6})[^/]*$

Explanation

• ^.*/ Make sure to match the last occurrence of /
• ( Capture group 1
  
  • [^/]{1,6} Match 6 times any char other than /
• ) Close group 1
• [^/]* Match 0+ occurrences of any char except /
• $ End of string

See a regex demo

Oracle Substring a column for last index of character

In instr function, if you use -1 at the last parameter, it means last occurrence of the char string.

instr(string, '_', -1) = last occurrence of _

Thus:

select substr('TEMP_ABC',1,instr('TEMP_ABC','_',-1)-1)
from dual;

Result: TEMP

select substr('TEMP_ABC_XYZ',1,instr('TEMP_ABC_XYZ','_',-1)-1)
from dual;

Result: TEMP_ABC

Parsing out text after final _

You can use INSTR and SUBSTR together to get what you want:

strLast_name := SUBSTR(SOME_FIELD, INSTR(SOME_FIELD, '_', -1)+1);

will return 'ddd' if SOME_FIELD contains 'aaa_bbb_ccc_ddd`.

Best of luck.

Using Regex_substr in Oracle to select string up to the last occurence of a space within \n characters length

I've solved with instr/substr:

select substr(column_a,1,instr(substr(column_a,1,40), ' ', -1 )) column1,
       substr(column_a,instr(substr(column_a,1,40), ' ', -1 )+1, 40) column2  
from table1

---

Related Topics