Extract only uppercase text from db field

I "inherited" a db with a field in a table where there are lowercase and uppercase mixed together, eg.

gateway 71, HOWARD BLVD, Chispa, NY

They aren't easily "splittable" with code because they don't always come in this form. What I need is a way to extract only uppercase letters. Is this possible with SQLite?

Answers

As NuSkooler mentions, this is probably easier and faster to do using a cursor; an especially attractive option if you will only have to do this once.

Here's a quick example (using built-in SQLite from Python REPL):

import sqlite3

with sqlite3.connect(":memory:") as conn:
    conn.execute('''create table t (c, newc);''')
    conn.commit()
    conn.execute('''insert into t (c) values (?);''', ('testing MAIN ST',))
    conn.commit() 
    results = conn.execute('select c from t;').fetchall()
    for line in results:
        tokens = line[0].split()
        filtered_tokens = [i for i in tokens if i.isupper()]
        newc = ' '.join(filtered_tokens)
        conn.execute('update t set newc = ?;',(newc,))
        conn.commit()

    conn.execute('''select c,newc from t;''').fetchone()
    # (u'testing MAIN ST', u'MAIN ST')

Posted on by mechanical_meat

This is a case where it may just be easier (and perhaps quicker) to SELECT with any additional WHERE requirements you may have and create a cursor to iterate over the results doing your uppercase checks in code.

Another option with SQLite would be to create a custom function, so you could do something like:

SELECT foo WHERE MYISALLUPPERFUNC(foo) = 1;
Posted on by NuSkooler