framework/resources/Functional/aggregates/aggregation/group_by_expression/with_string_functions_char_length.sql

-- coalesce with columns from side that does not produce nulls
-- string manipulation functions
-- char_length - not a calcite built in function
select 
	count(*),
        substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2),
	char_length(substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2))
from 
	t1 left outer join t2 on t1.a1 = t2.a2 
	left outer join t3 on t1.c1 = t3.c3
group by
        substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2),
	char_length(substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2))
order by
        substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2),
	char_length(substr(lower(UPPER(to_char(t1.c1, 'MMM-DD-YY'))), 5, 2))
;