Sometimes, we get to work with a text that has multiple sequential space characters that we don’t need, and hence need to remove. There are many ways to accomplish the task in pure Oracle SQL. Below, you can find 3 of them that I believe are worth mentioning.

We will be removing undesired spaces from the following character string:

Three Strategies for replacing multiple spaces

**Strategy #1: Regular Expressions**

Of course, every time we need to process some text data, Regular Expressions come to mind. Here, the choice is very natural and straightforward:

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ) SELECT REGEXP_REPLACE(text, '[ ]+',' ') text FROM x

Result:

TEXT ---------------------------------------------- Three Strategies for replacing multiple spaces

Regular expression [ ]+ finds all occurrences of sequential spaces and regexp_replace function substitutes each of such occurrences with a single space.

**Strategy #2: Triple Replace**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ) SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text FROM x

Result:

TEXT ---------------------------------------------- Three Strategies for replacing multiple spaces

To see how this trick works, let’s break it down in 3 steps:

**Step 1: Replace Spaces with Pipe-Space combination:**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ) SELECT REPLACE(text, ' ', '| ') text FROM x

Result:

Three| | | Strategies| | | | for| | | | replacing| | | | multiple| | | | | | | | | spaces

**Step 2: Remove all Space-Pipe combinations**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ) SELECTREPLACE(REPLACE(text, ' ', '| '), ' |')text FROM x

Result:

TEXT --------------------------------------------------- Three| Strategies| for| replacing| multiple| spaces

Note, that we first replaced each space with PIPE-SPACE sequence and then removed the opposite order sequence SPACE-PIPE, which left us with non-repeated occurrences of PIPE-Space combinations.

**Step 3: Remove PIPE characters**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ) SELECTREPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|')text FROM x

Result:

TEXT ---------------------------------------------- Three Strategies for replacing multiple spaces

## Strategy #3: Recursive WITH Clause

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual ), y(text) AS ( SELECT REPLACE(x.text, ' ', ' ') FROM x UNION ALL SELECT REPLACE(y.text, ' ', ' ') FROM y WHERE INSTR(y.text, ' ')>0 ) SELECT text FROM y WHERE INSTR(y.text, ' ')=0

Result:

TEXT ---------------------------------------------- Three Strategies for replacing multiple spaces

The idea is to replace two-space combination with a single space until no more two-space sequence will be left in the text.

## Final thoughts

What will change if we need to process not a single text value but a table/collection?

Let’s examine how each of the above strategies will work:

**Strategy #1: Regular Expressions**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual UNION ALL SELECT 'One more string with spaces ' FROM dual ) SELECT REGEXP_REPLACE(text, '[ ]+',' ') text FROM x

Result:

TEXT ----------------------------------------------- Three Strategies for replacing multiple spaces One more string with spaces

**Strategy #2: Triple Replace**

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual UNION ALL SELECT 'One more string with spaces ' FROM dual ) SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text FROM x

Result:

TEXT ----------------------------------------------- Three Strategies for replacing multiple spaces One more string with spaces

So far, so good.

### Strategy #3: Recursive WITH Clause

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual UNION ALL SELECT 'One more string with spaces ' FROM dual ), y(text) AS ( SELECT REPLACE(x.text, ' ', ' ') FROM x UNION ALL SELECT REPLACE(y.text, ' ', ' ') FROM y WHERE INSTR(y.text, ' ')>0 ) SELECT text FROM y WHERE INSTR(y.text, ' ')=0

Result:

TEXT ----------------------------------------------- One more string with spaces Three Strategies for replacing multiple spaces

All is good except for the order – the line with the smaller number of “double” spaces came first as it was first cleaned up. To preserve the original sort order we will need to make a change:

WITH x AS ( SELECT 'Three Strategies for replacing multiple spaces' AS text FROM dual UNION ALL SELECT 'One more string with spaces ' FROM dual ), y(text, rn) AS ( SELECT REPLACE(x.text, ' ', ' '), ROWNUMFROM x UNION ALL SELECT REPLACE(y.text, ' ', ' '), y.rnFROM y WHERE INSTR(y.text, ' ')>0 ) SELECT text FROM y WHERE INSTR(y.text, ' ')=0ORDER BY rn

Result:

TEXT ----------------------------------------------- Three Strategies for replacing multiple spaces One more string with spaces

Now, it’s all good!

**My Oracle Group on Facebook:**

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

**Would you like to read about many more tricks and puzzles?**

For more tricks and cool techniques check my book **“Oracle SQL Tricks and Workarounds”**.