How do I search for a certain text pattern in column data when coding in python using the sqlite3 package?
Asked
Active
Viewed 105 times
-3
-
5You might find [this question](http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) interesting. – Aaron Christiansen Apr 09 '16 at 19:52
-
Can you add an example of what you mean? – Padraic Cunningham Apr 09 '16 at 20:13
-
Do you mean that you can access MySQL with a sqllite3 module? This is new to me – Pullie Apr 09 '16 at 20:24
-
for example, I want to look for transactions of a certain month in a table, which has a 'transaction date' column but not in standard format.So, you could have transactions for month of May looking anything like one of these: 08-May-2015 or 08May15 or 05-08-2015 or may 08, 2015. So, I want to write a regular expression that has 'May' or 'may' in it with possibility of characters before and after. then using the 'execute' command of sqlite3 package, using 'SELECT' in SQL, only select those entries that have transaction date in May. .... I hope that helps explain what I am trying to do. Thanks! – Elle Ameli Apr 09 '16 at 20:26
-
1@Pullie: Yes! .... do something like this: import sqlite3 conn = sqlite3.connect('aDB.db') c = conn.cursor() c.execute('SELECT name FROM sqlite_master WHERE type=\'table\'') #list all tables in the database print c.fetchall() c.execute('SELECT sql FROM sqlite_master WHERE type=\'table\' AND name=\'sales\'') #list columns in table 'sales' print c.fetchall() conn.close() – Elle Ameli Apr 09 '16 at 20:26
-
1Why are dates allowed to be used in different formats? It would be easier to only allow dates to be one format. – ryanmoir Apr 09 '16 at 21:05
-
Are you asking someone to tell you how to write the regex to match those date formats, or are you asking someone to tell you how to use the re function? – Rumbles Apr 09 '16 at 21:43
-
@Rumbles: The latter. ... with the execute command, I don't know how to place a regular expression inside the SQL select command so that it would be interpreted as a RegEx and not taken literally! – Elle Ameli Apr 09 '16 at 22:05
-
I suggest you change your tags to show the right db – Rumbles Apr 09 '16 at 22:12
1 Answers
0
From this question How do I use regex in a SQLite query?
SQLite3 supports the REGEXP operator:
WHERE x REGEXP <regex>
-
If you felt that a previous answer in its entirety fully answers this question, flagging this as a duplicate might be a better approach than replicating content without bringing anything new on the table. – Reti43 Apr 09 '16 at 22:25
-