CODEWITHSUNDEEP
sqloracle-database
bazyl
bazyl

Reputation: 263

Retrieve number from string most effective way

I'm wondering if there is better,more optimal way to retrieve a number from string eg.

"O5_KK/text1/1239312/006_textrandom"
"O5_KK/text1/1239315/0109_textrandom123"
"O5_KK/text1/1239318/0110_textrandom432"

'O5_KK/text1' - hardcoded, never change.
1239312,1239315,1239318 - random number, unique within row
textrandom,textrandom123,textrandom432 - random string

as output I would like to get only numbers:

006
0109
0110

I know how to do it by using instr,substr,replace function. But it looks terrible to read. I'm looking for other solution, any hints ?

Thanks

Upvotes: 0

Views: 65

Answers (3)

user5683823
user5683823

Reputation:

Assuming the number you need is always between the last slash(/) and the last underscore (_), with no characters in between - as in your sample - the best solution uses just substr and instr. Note that regular expressions are slower than straight substr and instr; there are cases when the only solution is regexpr (or regexpr is much easier to write and maintain), but this is not one of those cases.

select substr(val, instr(val,'/',-1)+1, instr(val,'_',-1)-instr(val,'/',-1)-1)

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95090

This is simply the part after the third slash before the second underscore:

substr(str, instr(str, '/', 1, 3) + 1, instr(str, '_', 1, 2) - instr(str, '/', 1, 3) - 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use regexp_subtr():

select regexp_substr(val, '/[0-9]+_', 1, 1)

And then remove the extra characters:

select replace(replace(regexp_substr(val, '/[0-9]+_', 1, 1), '/', ''), '_', '')

Upvotes: 5

Related Questions