If I run the following query I get the results as expected:
SELECT upper(substr(columnname, 1, 1))
FROM table
GROUP BY upper(substr(columnname, 1, 1)).
I get groups for every starting letter from A to Z and 0 to 9. But what I want to have is that I get groups like "0-9", "A-C", "D-F" and so on. I have no ideas how to group groups. Can anybody give me a hint how to start please?
One idea could be to create a user-defined function that receives one letter and returns a string with the group to which the letter belongs.
If you name that function "getGroupFor" and it works so that:
getGroupFor('A') returns 'A-C'
getGroupFor('B') returns 'A-C'
... (and so on)
Then you can change your select to group by the output of that function.
SELECT getGroupFor(upper(substr(columnname, 1, 1))) FROM table GROUP BY getGroupFor(upper(substr(columnname, 1, 1)))
You can find how to create user-defined functions for SQLLite by looking at this other question:
How to create custom functions in SQLite
Another way would be to create a table that can map letters to letter-groups, then join the tables and group by the "letter-group" column instead of by the individual letters.
