How to Get String After Character Oracle

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.

How to Select a substring in Oracle SQL up to a specific character?

Using a combination of SUBSTR, INSTR, and NVL (for strings without an underscore) will return what you want:

SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output
  FROM DUAL

Result:

output
------
ABC

Use:

SELECT NVL(SUBSTR(t.column, 0, INSTR(t.column, '_')-1), t.column) AS output
  FROM YOUR_TABLE t

Reference:

• SUBSTR
• INSTR

Addendum

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

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

Need to extract everything before/after substring in Oracle SQL

If it will really always say 'IMPORTER:' then you can use substr() to get the sub-strings, and instr() to figure out how much to get:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as part1,
  substr(farmbol, instr(farmbol, 'IMPORTER:')) as part2
from t;

PART1        PART2            
------------ ------------------
20394823409  IMPORTER: John Doe

You can manipulate the extracted values, e.g. trimming the trailing space from the first part, or converting it to a number if it always will be:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as part1,
  trim(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1)) as part1_trim,
  cast(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as number) as part1_num,
  substr(farmbol, instr(farmbol, 'IMPORTER:')) as part2
from t;

PART1        PART1_TRIM     PART1_NUM PART2            
------------ ----------- ------------ ------------------
20394823409  20394823409  20394823409 IMPORTER: John Doe

And if you don't really want to keep the 'IMPORTER:' part of the string you can adjust the starting position of the second substring, by the length of that fixed value:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select cast(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as number) as part1_num,
  trim(substr(farmbol, instr(farmbol, 'IMPORTER:') + 9)) as importer
from t;

   PART1_NUM IMPORTER
------------ --------
 20394823409 John Doe

If you needed a more flexible pattern then you could use regular expressions instead, but that seems like overkill here.

Having to do this suggests you should really be storing this data in separate columns to start with, possibly with different data types, rather than mashed together in a single string column.

Oracle Substring after specific character

regexp_replace(string, '^.*?-(\d+)[LR].*$', '\1')

fiddle

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

Substring after nth occurrence of substring in a string in oracle

This might be one option:

• regexp_count finds number of <br /> strings within the column (so you'll have to subtract 2 from that number to get the last 3 lines
• the rest is simple; use SUBSTR which starts from the previously calculated position and return the rest of the column value

---

SQL> select
  2    substr(col, instr(col, '<br />', 1, regexp_count(col, '<br />') - 2) + 6) result
  3  from test;

RESULT
--------------------------------------------------------------------------------
12/10 10:43:32:  - 200000 subs exported<br />12/10 10:43:32:  - 200400 subs expo

SQL>

How to get a substring after first and second white space in Oracle

You could do this with a regular expression, to get the first string of consecutive digits from your value:

regexp_substr(RC_POLICY_NO, '\d+')

or all characters between the first and second block of spaces:

regexp_substr(RC_POLICY_NO, '[^ ]+')

Quick demo:

with t (RC_POLICY_NO) as (select '  125896547        0101' from dual)
select regexp_substr(RC_POLICY_NO, '\d+'), regexp_substr(RC_POLICY_NO, '[^ ]+')
from t;

REGEXP_SU REGEXP_SU
--------- ---------
125896547 125896547

You can so it with normal substr(), but you need to search for a spave rather than null as you are currently doing; and to remove the leading spaces you can trim it first:

substr(ltrim(RC_POLICY_NO), 1, instr(ltrim(RC_POLICY_NO), ' ') - 1)

Demo of that approach:

with t (RC_POLICY_NO) as (select '  125896547        0101' from dual)
select substr(ltrim(RC_POLICY_NO), 1, instr(ltrim(RC_POLICY_NO), ' ') - 1)
from t;

SUBSTR(LT
---------
125896547

That assumes there will always be a space after the string you want; if you might be starting from a value that doesn't have the two blocks of characters then you'd need to do a bit more work.

if this is something you will do a lot, it might be worth adding a virtual column and/or function-based index so you can search for specific values more easily.

---

Related Topics