Get 5 Characters After Last Slash

Question

I need the first 5 characters after the last slash. I can handle to get all symbols after slash, but fail to take only the first 5 of them.

REGEX_SUBSTR('people/capital/base/flagedbyloggers','[^/]*$')

With [^/]*$, I get flagedbyloggers, but I need only flaged, first 5 symbols.

With [^/]{5}$, I get ggers, which is 5 last symbols after the last slash, but I need flaged, first 5 symbols after the last slash.

Also sometimes the string after last slash can be shorter, so it has to work with less then 5 characters, e.g. people/capital/base/no should return only no.

The environment is ORACLE database. Same result I have with online tool https://regexr.com/ .

What is the environment? Try [`([^/]{6})[^/]*$`](https://regex101.com/r/Fc0cAi/2) — Wiktor Stribiżew, Dec 14 '20 at 22:56
I think you need 6 characters right? `^.*\/(.{6})` https://regex101.com/r/ysmdud/1 — The fourth bird, Dec 14 '20 at 22:57
_"I don't think environment matters"_ It _does_ matter. Whenever you post a regex question, it's important to include the programming language, tool, or regex flavor that you're using. That's because not all regex features are supported by all flavors. Syntax can be different too. — 41686d6564 stands w. Palestine, Dec 14 '20 at 23:18

score 2 · Answer 1 · answered Dec 14 '20 at 23:45

2

You don't need a regular expression. You can use simple string functions:

SELECT SUBSTR( value, INSTR( value, '/', -1 ) + 1, 6 ) AS substring
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'people/capital/base/flagedbyloggers' FROM DUAL;

Outputs:

| SUBSTRING |
| :-------- |
| flaged    |

db<>fiddle here

answered Dec 14 '20 at 23:45

MT0

143,790
11
59
117

Thank you for your answer. I prefer regex, because they are universal and can be reused in backend/frontend code. +1 – TPAKTOPA Dec 14 '20 at 23:48
@TPAKTOPA They are not guaranteed to be universal; Oracle does not support the full range of regular expression syntax (for example, you cannot use non-capturing groups or match on word boundaries). String functions are pretty universal, they may have different names between front- and back-end but when you know what you want to achieve then finding a substring of length 6 starting at the character after the index of the last slash character is going to be pretty easy to translate from Oracle database to Python/Java/C++/etc. (middle-tier) to JavaScript (client-side). – MT0 Dec 14 '20 at 23:54

The fourth bird · Accepted Answer · 2020-12-14T23:30:48.830

1

You could match until the last occurrence of a / with a capturing group and a quantifier {1,6} to match no or flaged

Note that flaged are 6 characters instead of 5 after the /

This page shows how to get the capturing group from the match using either REGEXP_SUBSTR or REGEXP_REPLACE.

^.*/([^/]{1,6})[^/]*$

Explanation

^.*/ Make sure to match the last occurrence of /
( Capture group 1
- [^/]{1,6} Match 6 times any char other than /
) Close group 1
[^/]* Match 0+ occurrences of any char except /
$ End of string

See a regex demo

edited Dec 14 '20 at 23:30

answered Dec 14 '20 at 23:12

The fourth bird

154,723
16
55
70

1

That works for all scenarios, thank you. Also looks like that mine regex demo site works incorrectly, so I will start to use your for demo/testing regex. – TPAKTOPA Dec 14 '20 at 23:47

Wiktor Stribiżew · Answer 3 · 2020-12-14T23:50:22.973

1

You can use the following REGEXP_SUBSTR code:

REGEXP_SUBSTR('people/capital/base/flagedbyloggers', '([^/]{1,6})[^/]*$', 1, 1, NULL, 1)

See the regex demo. Details:

([^/]{1,6}) - one to six chars other than / (Group 1, the last argument in the REGEXP_SUBSTR extracts this value)
[^/]*$ - any zero or more chars other than / up to the end of the string.

edited Dec 14 '20 at 23:50

answered Dec 14 '20 at 23:15

Wiktor Stribiżew

607,720
39
448
563

Thank for your answer, it works for long last string, but not for a shorter one. Also, parameters for function helped. +1 – TPAKTOPA Dec 14 '20 at 23:31
1

@TPAKTOPA I changed the quantifier. It was actually not a problem since the solution worked for the current OP. – Wiktor Stribiżew Dec 14 '20 at 23:50
My bad, I edited post and added later, that it should work with shorter text too. – TPAKTOPA Dec 15 '20 at 00:02

score 0 · Answer 4 · answered Dec 14 '20 at 23:17

0

If except slash characters are words, you can use the below statement.

/(\w{5})\w*$

Regex101 Demo

answered Dec 14 '20 at 23:17

Ismail Durmaz

2,521
1
6
19

Get 5 Characters After Last Slash

4 Answers4