Three Strategies for replacing multiple spaces with single ones.

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
)
SELECT REPLACE(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
)
SELECT REPLACE(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, '  ', ' '), ROWNUM
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' '), y.rn
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0
ORDER BY rn
```

Result:

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

Now, it’s all good!