python, sqlite3, re, regular expressions

sqlite_re.py

import re
import sqlite3

def re_search(expr, item):
    return re.search(expr, item, flags=re.I) is not None

conn = sqlite3.connect(':memory:')
conn.create_function('regsub', 3, re.sub)
conn.create_function('regexp', 2, re_search)

cursor = conn.cursor()

cursor.executescript('''
    CREATE TABLE shows (
        id INTEGER NOT NULL, episode INTEGER, season INTEGER, title TEXT, timestamp INTEGER, screen_size TEXT,
        PRIMARY KEY (id)
    );
    INSERT INTO "shows" VALUES(1,2,2,'12 Monkeys',1461632400,NULL);
    INSERT INTO "shows" VALUES(1507,21,4,'Person of Interest',1430272800,NULL);
    INSERT INTO "shows" VALUES(1508,22,4,'Person of Interest',1430877600,NULL);
    INSERT INTO "shows" VALUES(1509,1,1,'Black Mirror',1323032400,NULL);
''')

for title, in cursor.execute('select title from shows'):
    print(title)

print('-' * 100)

# replace spaces and digits with '_' for each row
cursor.execute('update shows set title = regsub(?, ?, title)', [r'[\s\d]', '_'])

# search records where title starts with 'B' or '_'
for title, in cursor.execute('select title from shows where title REGEXP ?', [r'^[B_]']):
    print(title)