-- Use recursive SQL to remove surplus space characters from text strings, leaving just one space between words.
-- This is incredibly useful, for example in tidying up name and address fields.
-- Alex Levy, Sustainable Software Ltd. http://www.sustainablesoftware.net
-- First written sometime in 2001.

--The SQL below uses this example table: 
--create table alex.stripblanks
--(line1 char(50) not null,
-- line_no smallint not null)
--in userspace1
--partitioning key (line_no)
--;
--insert into alex.stripblanks
--values ('lots     of     space                ',1),
--       ('    loadsa      space   in    here   ',2),
--       ('   spaced     out,     man!          ',3)
--;

with recurse (myline,linenum,start,level) as
             (select substr(ltrim(root.line1),1,50),line_no,1,0
              from   alex.stripblanks root
              UNION ALL
              select char(replace(myline,'  ',' '),50),
	            linenum,
	            parent.start + locate('  ',parent.myline,parent.start),
	            parent.level + 1
              from  recurse parent
              where locate('  ',parent.myline,parent.start) < 50 
	            AND parent.start < 50 ),
     temp    (linenum, level) as 
             (select linenum, max(level)
              from   recurse
              group by linenum ),
     result  (the_line) as
	           (select r.myline
	            from   recurse r, temp t
              where  r.linenum = t.linenum
	            and    r.level = t.level)
select * from result
;
     
