group by function in sqlite3 or itertools.groupy in python, sorting and grouping middle values within a string
Question: I have the following connection to a SQLite database returning data in the
"phone1" column, I'm looking to skip the area code portion and order by the
exchange (middle value) portion of phone1 column. Implementing wildcards seems
to work if you're testing for a known exchange, however I'd like group by or
order by starting with the 5 string character.

first_name, last_name, company, address, city, county, state, zip, **phone1**
, phone2, email, web

    
    
        (0, ('201-238-5688',))
        (1, ('201-431-2989',))
        (2, ('201-474-4924',))
        (3, ('201-588-7810',))
        (4, ('201-672-1553',))
    
        .....
        (495, ('973-943-3423',))
        (496, ('978-626-2978',))
        (497, ('978-697-6263',))
        (498, ('979-718-8968',))
        (499, ('985-890-7262',))
    
    import os, csv, json, re
    import sqlite3
    
    
        conn = sqlite3.connect('US_500.sqlite')
        conn.text_factory = str
        cursor = conn.cursor()
        reader = cursor.execute ("SELECT phone1 FROM SampleData ORDER BY substr(phone1, 5) BETWEEN 200 AND 300")
    
        tabledata = cursor.fetchall()
        for row in enumerate(tabledata):
            print str(row)
    
        conn.close()
    


Answer: I don't think that you need to group, ordering should be sufficient.

In a SQL query you can use `substr()` to select part of a field, and then
order by that. Your query could be:

    
    
    SELECT phone1 FROM SampleData order by substr(phone1, 5, 3)
    

This extracts 3 characters starting at the 5th position (so characters 5, 6 &
7) and uses them for ordering.

Similarly you can also do it in Python by calling `sorted()` on the query
results and passing a key function that selects the range of characters by
which to sort:

    
    
    tabledata = cursor.fetchall()
    for row in enumerate(sorted(tabledata, key=lambda x: x[4:7])):
        print str(row)
    

