ORACLE - regular expressions-CodePudding

Hi I have a string column which need to extract the third word as string:

example:

select notes from prod.client;


1024 xyxyxyx 138 1025 eivneini 345 4. expected result=138
1028 swswsws 345 24090 bububu_ 1 2.   expected result=345
1028 acboru 345 1050 xwindiwf. 1 2.  expected result=345
1028 vnufenu 345 350 dnwufbuw 1 2.  expected result=345
1113 abudbu 138 1114 bububuw 12.5 1. expected result=138

the result should be a string value.

Any idea how to create a regular expression that pick this?

regards

CodePudding user response：

You can use regexp_substr(). For the third string:

select regexp_substr(notes, '[^ ] ', 1, 3)
from prod.client;

Here is a db<>fiddle.

CodePudding user response：

You need to define "word". Are all "words" in your strings separated by spaces, and everything that is not a space is part of a word?

For example: is result=138 a single word, or is it two "words" separated by the equal sign?

If "words are separated by space, and everything that is not a space is part of a word", then Gordon Linoff's solution is correct.

But be aware of the possible problems. If that's your definition, then the string Hello, world! has two words: Hello, (including the comma!) and world! (including the exclamation point). Most applications don't use such a definition of word.

So, back to my first observation, at the top of this Reply. Define "word".

The convention, in regular expressions, is to define "word" as a maximal substring of consecutive characters that are either letters or digits or the underscore. That is represented by the escape sequence \w (in the POSIX standard and in Oracle regular expressions).

So, your answer should be something like

select regexp_substr(notes, '\w ', 1, 3)
from   ........

If your definition is different - for example if you must also allow hyphen and apostrophe - then you need to modify to something like

select regexp_substr(notes, '[[:alnum:]_''-] ', 1, 3)
from   ........

Notice the two apostrophes used to represent a single literal one, and remember that the hyphen must always appear first or last in a bracketed expression to stand for itself, otherwise it is a metacharacter.