Skip to content
Advertisement

ORA-01722: invalid number regexp [closed]

I have the below bit of code:

WITH aux ( str ) AS (
    SELECT
        'A     b s         d'
    FROM
        dual
)
SELECT
    regexp_substr(str, 'a', 'n') reg
FROM
    aux;

However, I’m getting this error:

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

My current numeric character configuration is .,

SELECT
    *
FROM
    nls_session_parameters
WHERE
    parameter = 'NLS_NUMERIC_CHARACTERS';

PARAMETER               VALUE
---------               --
NLS_NUMERIC_CHARACTERS  .,

Advertisement

Answer

The third argument to regexp_substr() is a number that specifies the position where to start searching.

If you are trying to specify the match_parameter(), then you need to specify all five arguments:

    regexp_substr(str, 'a', 1, 1, 'n') as reg
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement