How to get file name without extension with using Regular Expressions

Question

I have a field with following values, now i want to extract only those rows with "xyz" in the field value mentioned below, can you please help?

        Mydata_xyz_aug21


        Mydata2_zzz_aug22


        Mydata3_xyz_aug33

One more requirement

I want to extract only "aIBM_MyProjectFile" from following string below, can you please help me with this?

finaldata/mydata/aIBM_MyProjectFile.exe.ld

I've tried this but it didn't work.

select 
regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','([^/]*)[\.]') exp 
from dual;

didn't work @ran_0315 returned aIBM_MyProjectFile.exe.ld But, I want to get only aIBM_MyProjectFile — general46, Sep 14 '18 at 11:48
Try `WHERE REGEXP_LIKE (col, '^[^_]*_xyz_[^_]*$');` and `regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','.*/([^.]+)', 1, 1, NULL, 1)`. — Wiktor Stribiżew, Sep 14 '18 at 11:49
@WiktorStribiżew returned aIBM_MyProjectFile.exe in Oracle. Is there a way to get only the file name without its extension? — general46, Sep 14 '18 at 11:52
I updated my above comment suggestions, please check and let know if they work for you. — Wiktor Stribiżew, Sep 14 '18 at 11:55
Thank you @WiktorStribiżew regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','.*/([^.]+)', 1, 1, NULL, 1) worked! But, the first one did not worked . Could you please check this one. select regexp_substr('Mydata2_zzz_aug22','^[^_]*_xyz_[^_]*$') exp from dual; Did I do something wrong? — general46, Sep 14 '18 at 11:58
What do you mean to do with `regexp_substr` with `'^[^_]*_xyz_[^_]*$'`? What do you mean to get? A value between 2 `_`s? Try `regexp_substr('Mydata_xyz_aug21','_([^_]+)_', 1, 1, NULL, 1)` — Wiktor Stribiżew, Sep 14 '18 at 11:59
Try `regexp_substr('Mydata_xyz_aug21','_([^_]+)_', 1, 1, NULL, 1)` — Wiktor Stribiżew, Sep 14 '18 at 12:00
Hi again @WiktorStribiżew. Regexp_substr is one of Oracle SQL regular expression functions. That returns the substring related with the pattern. For example I wanna get zzz from that string — general46, Sep 14 '18 at 12:02
Ok, but read your question, you are asking for something different (*i want to extract only those **rows***) - please update the question. You want to extract substrings between two underscores. — Wiktor Stribiżew, Sep 14 '18 at 12:03

score 1 · Accepted Answer · answered Sep 14 '18 at 12:05

To extract substrings between the first pair of underscores, you need to use

regexp_substr('Mydata_xyz_aug21','_([^_]+)_', 1, 1, NULL, 1)

To get the file name without the extension, you need

regexp_substr('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld','.*/([^.]+)', 1, 1, NULL, 1)

Note that each regex contains a capturing group (a pattern inside (...)) and this value is accessed with the last 1 argument to the regexp_substr function.

The _([^_]+)_ pattern finds the first _, then places 1 or more chars other than _ into Group 1 and then matches another _.

The .*/([^.]+) pattern matches the whole text up to the last /, then captures 1 or more chars other than . into Group 1 using ([^.]+).

score 0 · Answer 2 · answered Sep 14 '18 at 12:07

For the first requirement, it would suffice to use LIKE, as posted in answer above:

SELECT column
  FROM table
 WHERE column LIKE '%xyz%';

For your second requirement (extraction) you will have to use REGEXP_SUBSTR function:

SELECT REGEXP_SUBSTR ('FinalProject/MyProject/aIBM_MyProjectFile.exe.ld', '.*/([^.]+)', 1, 1, NULL, 1) 
  FROM DUAL

I hope it helped!

score 0 · Answer 3 · answered Sep 14 '18 at 17:00

Another way to do this is to skip regexp completely:

WITH
    aset AS
        (SELECT 'with_extension.txt' txt FROM DUAL
         UNION ALL
         SELECT 'without_extension' FROM DUAL)
SELECT CASE
           WHEN INSTR (txt, '.', -1) > 0
           THEN
               SUBSTR (txt, 1, INSTR (txt, '.', -1) - 1)
           ELSE
               txt
       END
           txt
  FROM aset

The result of this is

with_extension
without_extension

A BIG Caveat where the regexp is better:

My method doesn't handle this case correctly:

\this\is.a\test

So after I have gone to all this effort, stay with the regexp solutions. I'll leave this here so that others may learn from it.

How to get file name without extension with using Regular Expressions

3 Answers3