best_match_sqlite.sql

WITH RECURSIVE testdata as (SELECT 'Max' Name UNION ALL 
                            SELECT 'Moritz' UNION ALL 
                            SELECT 'Maximilian' UNION ALL 
                            SELECT 'Maxim'),
     testdata_num  as (             
                  Select row_number() over ()   rn, name, length(name) len 
                  from testdata), 
      td_count(rn, len)  as (
                  SELECT rn,  len from testdata_num
                  UNION ALL 
                  SELECT rn, len-1 from td_count where len > 1  ), 
      testdata_compared as (
                  select tdn.name, 
                         tdn.rn, 
                         substr(tdn.name, 1, tdc.len) sub_name, 
                         tdn.len,
                         tdc.len as tdc_len, 
                         max ( instr(substr(tdn.name, 1, tdc.len), 'Maxima') , 
                         instr('Maxima', substr(tdn.name, 1, tdc.len) ) ) does_contain
                  from testdata_num tdn 
                    JOIN td_count tdc ON tdn.rn = tdc.rn ) ,
testdata_partitioned as (
                  select Name , tdc_len, max(tdc_len) over (PARTITION BY rn) max_len
                  from testdata_compared
                  where does_contain = 1)
select Name, max_len
from testdata_partitioned
where tdc_len = max_len
order by max_len
  ;