How to get file name without extension with using Regular Expressions

user_2761145 0
Viewed 935

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;
regexp-substr oracle sql regex
3 Answers

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 ([^.]+).

user_38329700

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!

user_101327860

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.

user_28004020
Related