CODEWITHSUNDEEP
sqloracle-databaseplsql
Pravin Hinge
Pravin Hinge

Reputation: 93

Find substring till last occurrence

I want to get the substring of a string like: filename_ip_time.pdf I want to select filename_ip, that is the string till last occurance of '_'

Upvotes: 2

Views: 9819

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Another way for the sake of argument using REGEXP_REPLACE():

SQL> select regexp_replace('filename_ip_time.pdf', '^(.*)_.*$', '\1') from dual;

REGEXP_REPL
-----------
filename_ip

SQL>

Where the regular expression can be read as: Return the first remembered group (\1) where the pattern matches:

^  Start of the line
(  start the first group to remember
.  any character
*  any number of the previous character (any character)
)  end the first group, followed by
_  A literal underscore
.*  followed by any number of any characters 
$  until the end of the line.

So in essence you are returning the first part of the string up to but not including the last underscore followed by the rest of the line. To me, clearer than nested substr() and instr() but you need to get your head around regular expressions, which will give you more power ultimately when you need to do more complex pattern matching.

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could do it simply using SUBSTR and INSTR.

For example,

Here INSTR will give the first position of _ from the end. 

SQL> WITH DATA AS(
  2  SELECT 'filename_ip_time.pdf' str FROM dual
  3  )
  4  SELECT SUBSTR(str, 1, instr(str, '_', -1, 1) -1) FROM DATA
  5  /

SUBSTR(STR,
-----------
filename_ip

SQL>

Or,

If you know that you need substr till the second occurrence of _, then use INSTR to get the position of second occurrence of _ from the start.

For example,

 SQL> WITH DATA AS(
  2  SELECT 'filename_ip_time.pdf' str FROM dual
  3  )
  4  SELECT SUBSTR(str, 1, instr(str, '_', 1, 2) -1) FROM DATA
  5  /

SUBSTR(STR,
-----------
filename_ip

SQL>

Upvotes: 0

ErikL
ErikL

Reputation: 2041

Use -1 as the start position with INSTR to start searching from the end of the string:

select INSTR('filename_ip_time.pdf', '_', -1) from dual

So if you want to select filename_ip, you should do something like this:

SELECT   SUBSTR ('filename_ip_time.pdf',
                 0,
                 (INSTR ('filename_ip_time.pdf', '_', -1)) - 1)
  FROM   DUAL

Upvotes: 5

Related Questions