-- generate test data
create table char_test
as
(select calendar_date - current_date as upi_id,
            trim(trailing from
                   substring('ksdfh asdfa asdfj as df asdfj asdfjha sdfgadhugadf vadf vad vaxdfgv yxc b sydfb  adfbv adf a fdv andfav sudfhasdv aasdh fasdf hadsf hasgf asdjfhad fgahdfgh asdasdfj  sdjfadsfjajdfgv ajds gfasdfj khasbdfjhajsdf adfj ahsdjkf asdhj jasdfasdhf asdf asd gfa dgf adf ga dfg ad fgadfg  adfg adfg adfg a gf ladfg alfaghnjdghjadf g ds afj adfgadf g adfg ad fgladfglad flgaldgn adg laldg adlg  ladlg aldg ladlfg adlf gadfg ad fgadlfg adgf lad lg ladl gl adg ladgl ladrgl lad lg ldad gl lad g lad lg  algl adlf gadfjl gladljfkgnjadg g jajdlf gadjf gahndf gadjgn ldfg'
                            from random(1,500)
                            for random(10,256)
                           )
                   ) as char_field
 from sys_calendar.calendar
 where upi_id between 1 and 2000
) with data
unique primary index (upi_id);


-- one SQL with product join to get right id's
-- is generic but might not show best performance espacially for big varchar fields
select t.upi_id,
          coalesce(c.id,0) as last_position,
          substring(char_field from last_position + 1) as last_field,
          char_field
from char_test t
        left outer join
        (select ci.calendar_date-current_date as id
         from sys_calendar.calendar ci,
                 (select max(characters(char_field)) as max_len from char_test) m
         where id between 1 and m.max_len
        ) c
		on index(t.char_field,' ') > 0
		     and last_position between index(t.char_field,' ') and characters(t.char_field)
where (last_position is null
	  or last_position between index(t.char_field,' ') and characters(t.char_field)
	  )
qualify max(case when substring(char_field from last_position for 1) = ' ' then last_position else null end) over (partition by upi_id) = last_position or last_position = 0
order by upi_id
;

--generate sql with case 
--generated sql should show better performance
select sql_text
from
(
	select 99999999 as order_id,
		  cast('select 	upi_id,
				case' as varchar(10000) )as sql_text
		from sys_calendar.calendar 
		where calendar_date = current_date
		union all
		select (c.calendar_date - current_date) as order_id,
		           '		when characters(char_field) > ' !! order_id !! ' and substring(char_field from ' !! order_id !! ' for 1) = '' '' then ' !! order_id as sql_text
		from (select max(characters(char_field)) as max_len from char_test) as t,
		        sys_calendar.calendar c
		where order_id between 1 and max_len
		union all
		select 0 as order_id,
		'
		            else 0
		           end as last_position,
		           substring(char_field from last_position + 1) as last_field,
		           char_field
		from char_test
		order by upi_id;' as sql_text
		from sys_calendar.calendar 
		where calendar_date = current_date
 	) as tmp
order by order_id desc;

-- paste SQL and execute here

-- drop example table
drop table char_test;