Non-integer starting_position in regexp_substr PL / SQL

oracle regexp_substr split
oracle regexp_substr between two strings
regexp_substr in oracle
oracle regexp_substr multiple matches
oracle regexp_substr between two characters
oracle regexp_substr after string
regexp_substr level
regexp_instr

I have concatenated strings from 3 different columns, like these:

salary total 8140 Walter Davis
salary absolute 975004 Nathan Johns
monthly total 11 Emily Grand

I want to write out just the names from every row. The problem is, the starting position of the names are not the same, so I can not write a simple number to the starting position parameter in regexp_replace.

So what I want to see is that:

Walter Davis
Nathan Johns
Emily Grand

My code is:

select 
regexp_substr(concat(concat(e.column1, e.column2), e.column3), '\w+',1,'\d+\w')
from exampletable e

'\w+' : With this, I want to write out all the words '\d+\w' : This would be the starting position, in my mind, it means the first word character after the digits, which is the first character of the names.

But I get these error messages: ORA-12801: ORA-01722

Thank you in advance!

Oracle REGEXP_SUBSTR, This tutorial shows you how to use the Oracle REGEXP_SUBSTR() to extract a is positive integer that indicates the starting position in the source string where the REGEXP_SUBSTR() function will not use the period to match the newline character. Oracle Implicit Statement Results · Calling PL/SQL Stored Functions in  The Oracle REGEXP_SUBSTR() function accepts 6 arguments: 1) source_string. is a string to be searched for. 2) pattern. is the regular expression pattern that is used to search for in the source string. 3) start_position. is positive integer that indicates the starting position in the source string where the search begins.

Try

regexp_replace(name,'\D+\d+(\D+)','\1')

\d+ - one or more digit

\D+ - one or more non-digit

\1 - first captured group within ()

Demo

Oracle / PLSQL: REGEXP_INSTR Function, The Oracle / PLSQL REGEXP_INSTR function is an extension of the INSTR It is an integervalue from 0 to 9 indicating the subexpression to match on in pattern. If the REGEXP_INSTR function does not find any occurrence of pattern, it will We could change the starting position of the search so that we perform the  Examples. The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

Just use regexp_replace

( main point of interest is digits to be included for purpose of seperating the string ) :

 create table exampletable( column_common varchar2(500) );

insert all 
        into exampletable values('salary total 8140 Walter Davis')
        into exampletable values('salary absolute 975004 Nathan Johns')
        into exampletable values('monthly total 11 Emily Grand')
 select * from dual;       

 select regexp_replace(column_common,'^(\D*)(\d*)') "Name"
   from exampletable;

SQL Fiddle Demo

Using Regular Expressions in Database Applications, Function that returns an integer that indicates the starting position of the given pattern in The expression a{3} matches the string aaa , but does not match aa . For the REGEXP_REPLACE function, Oracle SQL supports back references in​  occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern. match_parameter is a text literal that lets you change the default matching behavior of the function.

Are the names always last, following one or more digits? If so then you can do something like the following:

WITH x ( concat_str ) AS (
    SELECT 'salary total 8140 Walter Davis' FROM dual
     UNION ALL
    SELECT 'salary absolute 975004 Nathan Johns' FROM dual
     UNION ALL
    SELECT 'monthly total 11 Emily Grand' FROM dual
)
SELECT TRIM(REGEXP_SUBSTR(concat_str, '\D+$'))
  FROM x;

Returns:

Walter Davis
Nathan Johns
Emily Grand

EDIT: Just FYI, there's no reason to use the CONCAT() function in Oracle - just use the concatenation operator ||:

concat(concat(e.column1, e.column2), e.column3

==>

e.column1 || e.column2 || e.column3

Hope this helps.

REGEXP_INSTR, Database SQL Language Reference a positive integer indicating which occurrence of pattern in source_char Oracle should INSTR and REGEXP_SUBSTR the starting position (default) of the sixth occurrence of one or more non-blank  The Oracle/PLSQL REGEXP_SUBSTR function is an extension of the SUBSTR function. This function, introduced in Oracle 10g, will allow you to extract a substring from a string using regular expression pattern matching.

Oracle REGEXP_INSTR function, Oracle REGEXP_INSTR function : The Oracle REGEXP_INSTR function is used to return If no match is found, then the function returns 0. The position is a positive integer indicating the character of Searching started at the first character in the string and returns the starting position (default) of the third  Examples: Oracle REGEXP_SUBSTR function The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma and returns the substring, including the leading and trailing commas.

Oracle REGEXP_SUBSTR function, Oracle REGEXP_SUBSTR function : The REGEXP_SUBSTR function use NET Database SQL(2003 standard of ANSI) MySQL PostgreSQL SQLite This function returns NULL when no matches are found. position, The position is a positive integer indicating the character of source_char where Oracle  REM Extracting passenger names from flight information using REGEXP_SUBSTR

Oracle Regular Expression Examples for Professionals, Oracle Regular Expression Examples,Regular expression in oracle g The REGEXP_INSTR function that returns an integer that indicates the starting position of the This query will return value 6 so this mail id is not valid mail id. The REGEXP_REPLACE function returns the string, which replaces the  Question: What does the regexp_substr operator do? Can you show an example of using regexp_substr? Answer: The regexp_substr operator searches for a sub-string within a string. The REGEXP_SUBSTR function is the advanced version of the classic SUBSTR function, allowing us to search for strings based on a regular expression pattern.

Comments
  • It's not that clear to me. If you already have 3 different columns, why do you need to concatenate them and then separate? Is this an exercise? Also, are you sure you Always have a number before the name? That is, your need could be something like "everything after the number"?
  • So the 3 parameter is beacuse of the names starts on the third string? Can you tell me a solution if there would be rows where the name starts at the 4th string? And I also have the rows beyond with the names starts at the 3rd place. Thank you
  • so if we dont know which one is the name, just that it starts after the numbers
  • This is based on the fact that the name starts after the numeric part. the 3 parameter says to get the third part of the string, where the parts are 1: numeric part; 2: one or more spaces; 3: eveything after the numeric part and the space