Oracle REGEXP_REPLACE replace space in middle with empty string

Question

I'm trying to use the Oracle REGEXP_REPLACE function to replace a whitespace (which is in the middle of a string) with an empty string.

One of my columns contains strings like the following one.

• [alphanumeric][space][digits][space][alpha] (eg. R4SX 315 GFX)

Now, I need to replace ONLY the second whitespace (the whitespace after the digits) with an empty string (i.e. R4SX 315 GFX --> R4SX 315GFX)

To achieve this, I tried the following code:

SELECT REGEXP_REPLACE(
   'R4SX 315 GFX', 
   '([:alphanum:])\s(\d)\s([:alpha:])', 
   '\1 \2\3') "REPLACED" 
FROM dual;

However, the result that I get is the same as my input (i.e. R4SX 315 GFX). Can someone please tell me what I have done wrong and please point me in the right direction.

Thanks in advance.

Answer 1

[:alphanum:]

alphanum is incorrrect. The alphanumeric character class is [[:alnum:]].

You could use the following pattern in the REGEXP_REPLACE:

([[:alnum:]]{4})([[:space:]]{1})([[:digit:]]{3})([[:space:]]{1})([[:alpha:]]{3})

Using REGEXP

SQL> SELECT REGEXP_REPLACE('R4SX 315 GFX',
  2                        '([[:alnum:]]{4})([[:space:]]{1})([[:digit:]]{3})([[:space:]]{1})([[:alpha:]]{3})',
  3                        '\1\2\3\5')
  4  FROM DUAL;

REGEXP_REPL
-----------
R4SX 315GFX

SQL>

If you are not sure about the number of characters in each expression of the pattern, then you could do:

SQL> SELECT REGEXP_REPLACE('R4SX 315 GFX',
  2                        '([[:alnum:]]+[[:blank:]]+[[:digit:]]+)[[:blank:]]+([[:alpha:]]+)',
  3                        '\1\2')
  4  FROM dual;

REGEXP_REPL
-----------
R4SX 315GFX

SQL>

Using SUBSTR and INSTR

The same could be done with substr and instr which wouldbe less resource consuming than regexp.

SQL> WITH DATA AS
  2    ( SELECT 'R4SX 315 GFX' str FROM DUAL
  3    )
  4  SELECT SUBSTR(str, 1, instr(str, ' ', 1, 2) -1)
  5    ||SUBSTR(str, instr(str, ' ', 1, 2)       +1, LENGTH(str)-instr(str, ' ', 1, 2)) new_str
  6  FROM DATA;

NEW_STR
-----------
R4SX 315GFX

SQL>