Regexp substr starting position in different strings in PL\SQL

Question

I have strings like these:

16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo 

Bristol 2223 Anna Franklin

Soul 23333333 Henry Bayes

I want to get just the names from these strings. The fix thing is the names are always after the numbers, but as you can see, in the first string, there are numbers after the name John Smith. My code is now:

regexp_substr(string, '([0-9]+ )([^0-9]+\D)', 1, 1, 'i', 2))

The problem is, from the first string, I get the name, but there will be a whitespace character after that. How can I get just the name from the first string, without any whitespaces after it?

XING · Accepted Answer · 2018-06-11T09:51:11.663

You can use LTRIM to remove spaces from left side of string and RTRIM to remove spaces from right side. See below:

SELECT 
       regexp_substr('16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo', '([0-9]+ )([^0-9]+\D)', 1, 1, 'i', 2) Orig_Strng,
       ltrim(rtrim(regexp_substr('16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo', '([0-9]+ )([^0-9]+\D)', 1, 1, 'i', 2))) Spce_removed
FROM dual;

To check:

SELECT 
        LENGTH(regexp_substr('16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo', '([0-9]+ )([^0-9]+\D)', 1, 1, 'i', 2)) Orig_Strng,
        LENGTH(ltrim(rtrim(regexp_substr('16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo', '([0-9]+ )([^0-9]+\D)', 1, 1, 'i', 2)))) Spce_removed
from dual;

    Output:

    Orig_Strng    Spce_removed
    -----         -----
     20            19

Regexp substr starting position in different strings in PL\SQL

1 Answers1