CODEWITHSUNDEEP
oracle10g
user3661576
user3661576

Reputation: 51

RTRIM string ending with an incremental number

I am trying to narrow down the follow string to just the username. The number at the end is always different. I can LTRIM just fine, but when I try to use RTRIM I am having difficulty removing everything to the right of the username.

C:\documents and settings\[USERNAME]\my documents\reports\204452.pdf

Will RTRIM work in this instance? If not, a point in the right direction would be appreciated.

Thanks.

Upvotes: 0

Views: 44

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

If the username is always the third level of the full path, you can use a regular expression:

regexp_substr(<file path>, '[^\\]+', 1, 3)

For example:

select regexp_substr('C:\documents and settings\[USERNAME]\my documents\reports\204452.pdf', '[^\\]+', 1, 3)
from dual;

or using a subquery just to make it more readable:

select regexp_substr(file_path, '[^\\]+', 1, 3)
from (
  select 'C:\documents and settings\[USERNAME]\my documents\reports\204452.pdf'
  as file_path
  from dual
);

REGEXP_SUBSTR(FILE_PATH,'[^\\]+',1,3)
-------------------------------------
[USERNAME]

Note that the backslash has to be escaped in the pattern.

Upvotes: 1

Related Questions