Oracle conditionally adding spaces into data

Question

I have a table that was given to me with some 'incorrect' data. The format of the data should be:

"000  00000"

There are TWO spaces. WHERE the spaces are can be different for different records, so for example one record could be the previous example and another could be "00  00  0000". The problem is that the data came in, in some instances with only a single space. (so "000 00000").

Ideally, id like to do this in a query to fix the data that's been loaded via an update statement. If this is easier done outside of oracle, that's fine, I can re-load the data (its a bit of data, at almost 400,000 rows).

What would be the easiest way to find the single space and add another as needed, or leave it alone if there are already two spaces?

I am currently working on a query to split the string ON the spaces, trim the data then put it all back together with 2 spaces.... its not working out too well in testing.

Thanks in advance!

Answer 1

here is the query to find single space record , try making CASE statement as needed.

WITH sample_data AS (SELECT '000  00000' value FROM dual UNION ALL
                         SELECT '00  00  0000' value FROM dual UNION ALL
                        SELECT '000 00000' value FROM dual )


Select * from  sample_data where REGEXP_COUNT(VALUE,'[[:space:]]') =1