CODEWITHSUNDEEP
sqloracle-database
Matt K
Matt K

Reputation: 21

Oracle/SQL: Pull all data from the right of a ':' OR a '/', which ever comes first

I am looking for help with trying to figure out how I can re-write the following SQL to extract anything that comes after the first instance of a colon (:) or slash (/) coming from the right side of the field.

I currently use something like the below, but that will only pull from after the colon (:) and I want it to look for a slash (/) too:

select substr(DBURL,instr(DBURL,':',-1)+1) as DB
from databasename;

Examples of data in the DBURL field:

trld:client:blah:data
trld:client:blah/data

I am only looking to extract the data portion of the sting.

Upvotes: 0

Views: 110

Answers (2)

Kirill Leontev
Kirill Leontev

Reputation: 10941

Just wrap two of your instr into greatest.

11:17:02 (20)SYSTEM@dwal> ed
Wrote file S:\spool\dwal\BUF_SYSTEM_20.sql

  1  with databasename(dburl) as (
  2   select 'trld:client:blah:data' from dual union all
  3   select 'trld:client:blah\data' from dual
  4  )
  5  select substr(DBURL,greatest(instr(DBURL,'\',-1), instr(DBURL,':',-1))+1) as DB
  6* from databasename
11:17:14 (20)SYSTEM@dwal> /

DB
---------------------
data
data

Elapsed: 00:00:00.10

RegEx is ok either, but slower.

Upvotes: 2

Noel
Noel

Reputation: 10525

You can use Regular Expressions, specifically REGEXP_SUBSTR for this.

select regexp_substr(DBURL,            --source string
                    '[^:/]+$'          --pattern that you are looking for
                    ) as DB 
  from databasename;

Breaking down the pattern,

[^:/]  : match anything other than a colon or slash.
+      : match the preceding pattern one or more times
$      : match the end of the string.

Putting it togather, this pattern matches anything other than a colon or slash at the end of the string.

Upvotes: 2

Related Questions