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.

Thanks for the quick reply. But I tried that before posting this question. Still the same result. :( — Asela
– Asela, Commented May 27, 2015 at 10:39

Lalit Kumar B · Accepted Answer · 2015-05-27 10:56:39Z

2

[: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>

edited May 27, 2015 at 10:56

answered May 27, 2015 at 10:40

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lalit Kumar B Over a year ago

@Asela You're welcome! I have added another example using SUBSTR and INSTR.

Florin Ghita · Accepted Answer · 2017-12-30 16:54:06Z

2

Your regex contains an invalid class alphanum. Also, these classes must be used inside character classes [...]. Instead of \s, you need to use a supported [:blank:] class. More details on the regex syntax in MySQL can be found here.

I recommend using

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

This way you will use just 2 capturing groups. The less we have the better is for performance. Here you can see more details on REGEXP_REPLACE function.

edited Dec 30, 2017 at 16:54

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

answered May 27, 2015 at 10:51

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Collectives™ on Stack Overflow

Oracle REGEXP_REPLACE replace space in middle with empty string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related