CODEWITHSUNDEEP
oracle-databaseregex-negationword-boundary
Marium
Marium

Reputation: 253

Oracle Regex Exclude a Word (Only if it is found)

SOFTWARE: Oracle

I have a query that matches

EXCEL.EXE to EXCEL.EXE

Dropbox.exe to Dropbox.exe

1-2-3-4-hike.exe to 1-2-3-4-hike.exe

shish boom bah.exe to bah.exe

MS Outlook to MS Outlook

However, I wish to exclude the .exe.

In other words

EXCEL.EXE to EXCEL

Dropbox.exe to Dropbox

1-2-3-4-hike.exe to 1-2-3-4-hike

shish boom bah.exe to bah

MS Outlook to MS Outlook

Here is a previous working query:

CASE WHEN REGEXP_LIKE(M.ENDPOINTAPPLICATIONNAME, '\.exe', 'i')
     THEN REGEXP_SUBSTR(M.ENDPOINTAPPLICATIONNAME,'[a-zA-Z0-9]\S*\.exe',1,1,'i')
     ELSE M.ENDPOINTAPPLICATIONNAME
END

And here is my incorrect modified query - been trying !(.exe) among other things -- Please guide

CASE WHEN REGEXP_LIKE(M.ENDPOINTAPPLICATIONNAME, '\.exe', 'i')
     THEN REGEXP_SUBSTR(M.ENDPOINTAPPLICATIONNAME,'[a-zA-Z0-9]\S*\!(.exe)',1,1,'i')
     ELSE M.ENDPOINTAPPLICATIONNAME
END

Upvotes: 0

Views: 142

Answers (1)

Josh Eller
Josh Eller

Reputation: 2065

You can use regex capturing groups, with a group (parentheses) around your desired substring. For example:

with test_vals AS (
    SELECT 'EXCEL.EXE' AS ENDPOINTAPPLICATIONNAME FROM dual
    UNION ALL SELECT 'Dropbox.exe' FROM dual
    UNION ALL SELECT '1-2-3-4-hike.exe' FROM dual
    UNION ALL SELECT 'shish boom bah.exe' FROM dual
    UNION ALL SELECT 'MS Outlook' FROM dual
)

SELECT CASE WHEN REGEXP_LIKE(M.ENDPOINTAPPLICATIONNAME, '\.exe', 'i')
           THEN REGEXP_SUBSTR(
                  M.ENDPOINTAPPLICATIONNAME,'([a-zA-Z0-9]\S*)\.exe',1,1,'i',1 /* Only return result of first capturing group */
               )
           ELSE M.ENDPOINTAPPLICATIONNAME
       END
FROM test_vals m

Upvotes: 1

Related Questions