REGEXP_SUBSTR to split delimited values

First off, the {1}s are redundant, so it's really just:

^[aofdmep][a-z][a-z0-9]{4}a[sidbfkfpo]

This is actually a pretty simple patterh ... it's just looking at what the first 8 characters of the string are, and it's always a fixed length string, so you could possibly build a table of all of the permutations, and then do:

WHERE SUBSTR(string_to_match, 1, 8) IN (
  SELECT valid_prefixes FROM table_of_things_to_match
)

Unfortunately, it's 7*26*36^4*9 ... 2.75 billion possible combinations, but, still, fixed strings, so you could do:

WHERE SUBSTR(string_to_match, 1, 1) IN ('a','o','f','d','m','e','p')
  AND SUBSTR(string_to_match, 2, 1) IN ('a','b','c','d' ... 'z')
  AND SUBSTR(string_to_match, 3, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 4, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 5, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 6, 1) IN ('a','b','c','d' ... 'z','0','1' ... '9')
  AND SUBSTR(string_to_match, 7, 1) = 'a'
  AND SUBSTR(string_to_match, 8, 1) IN ('s','i','d','b','f','k','p','o')

(fill in the ... bits, of course)

oops, there's two fs in that last character class, so there's only 2.45 billion permutations.

I'm not going to pretend that it's going to be fast ... it likely won't be, but it'll get you the pattern you're looking for. If you tend to do this a lot, I'd likely build a table of characters so you had an easy way to select out alpha / numeric or alpha-numeric.

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string. The basic locate syntax you would need is explained here.

LOCATE (search str, str, [position])

search str = A string which will be searched for.

str =A string which is going to be searched.

position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here

SUBSTRING(str, pos, len)

str= A string.

pos= Starting position.

len= Length in characters.

The easier way of viewing this is to think of substring as the following SUBSTRING(str FROM pos FOR len)

The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.

declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)