Removing Multiple Random Spaces

Analytics
Enthusiast

Removing Multiple Random Spaces

I have a column on a table that has leading spaces and multiple spaces located at different positions in the data that need to be removed. I've tried the SQL below that I found on the forum but its only removing one space per cust_id.

select
case (position (' ' in cust_id) (named MY_POS))
when 0
then ltrim(cust_id)
else
substring(cust_id from 1 for MY_POS - 1) || (' ')
|| substring(cust_id from MY_POS + 1)
end as cust_id
from my_table

Here are some examples of the data
7711R 188235
8919Z 117198
5837D 621212
9132N 870656
53452 096025
PPZM0016126068
DRS 0042541667
5319XV113001
R990487290
ACP CXRJ5811826422
003526777
7701N 979360
L010022200
7709HO742831
ACP XRJ 7250906999
GRK FACD7810199726