How do I use regex in a SQLite query

Question

I d like to use a regular expression in sqlite  but I don t know how   My table has got a column with strings like this   3 12 13 14 19 28 32  Now if I type  where x LIKE  3   I also get the rows which contain values like 13 or 32  but I d like to get only the rows which have exactly the value 3 in that string   Does anyone know how to solve this

User · Answer

With python  assuming con is the connection to SQLite  you can define the required UDF by writing   con create function  regexp   2  lambda x  y  1 if re search x y  else 0    Here is a more complete example   import re import sqlite3  with sqlite3 connect   memory    as con      con create function  regexp   2  lambda x  y  1 if re search x y  else 0      cursor   con cursor                 cursor execute  SELECT   from person WHERE surname REGEXP   A

User · Answer

You could use a regular expression with REGEXP  but that is a silly way to do an exact match   You should just say WHERE x    3

User · Answer

You may consider also   WHERE x REGEXP      D 1  3  D 1        This will allow find number 3 in any string at any position

User · Answer

SQLite3 supports the REGEXP operator   WHERE x REGEXP  lt regex gt    http   www sqlite org lang expr html regexp

User · Answer

I don t it is good to answer a question which was posted almost an year ago  But I am writing this for those who think that Sqlite itself provide the function REGEXP     One basic requirement to invoke the function REGEXP in sqlite is  You should create your own function in the application and then provide the callback link to the sqlite driver   For that you have to use sqlite create function  C interface   You can find the detail from here and here

User · Answer

My solution in Python with sqlite3  import sqlite3 import re  def match expr  item       return re match expr  item  is not None  conn   sqlite3 connect   memory    conn create function  quot MATCHES quot   2  match  cursor   conn cursor   cursor execute  quot SELECT MATCHES   b    busy    quot   print cursor fetchone   0   cursor close   conn close    If regex matches  the output would be 1  otherwise 0

User · Answer

If you are using php you can add any function to your sql statement by using  SQLite3  createFunction  In PDO you can use PDO  sqliteCreateFunction and implement the preg match function within your statement   See how its done by Havalite  RegExp in SqLite using Php

User · Answer

As others pointed out already  REGEXP calls a user defined function which must first be defined and loaded into the the database  Maybe some sqlite distributions or GUI tools include it by default  but my Ubuntu install did not  The solution was  sudo apt-get install sqlite3-pcre   which implements Perl regular expressions in a loadable module in  usr lib sqlite3 pcre so  To be able to use it  you have to load it each time you open the database    load  usr lib sqlite3 pcre so   Or you could put that line into your    sqliterc   Now you can query like this   SELECT fld FROM tbl WHERE fld REGEXP   b3 b     If you want to query directly from the command-line  you can use the -cmd switch to load the library before your SQL   sqlite3   filename  -cmd   load  usr lib sqlite3 pcre so   SELECT fld FROM tbl WHERE fld REGEXP   b3 b      If you are on Windows  I guess a similar  dll file should be available somewhere

User · Answer

for rails              db   ActiveRecord  Base connection raw connection             db create function  regexp   2  do  func  pattern  expression                func result   expression to s match Regexp new pattern to s  Regexp  IGNORECASE     1   0             end

User · Answer

SQLite does not contain regular expression functionality by default   It defines a REGEXP operator  but this will fail with an error message unless you or your framework define a user function called regexp    How you do this will depend on your platform   If you have a regexp   function defined  you can match an arbitrary integer from a comma-separated list like so       WHERE your column REGEXP   b     your integer      b     But really  it looks like you would find things a whole lot easier if you normalised your database structure by replacing those groups within a single column with a separate row for each number in the comma-separated list  Then you could not only use the   operator instead of a regular expression  but also use more powerful relational tools like joins that SQL provides for you

User · Answer

A hacky way to solve it without regex is where        x        like    3

User · Answer

In Julia  the model to follow can be illustrated as follows   using SQLite using DataFrames  db   SQLite DB   lt name gt  db    register db  SQLite regexp  nargs 2  name  regexp    SQLite Query db   SELECT   FROM test WHERE name REGEXP   h       gt  DataFrame

User · Answer

In case if someone looking non-regex condition for Android Sqlite  like this string  1 2 3 4 5  then don t forget to add bracket     same for other special characters like parenthesis     in  phyatt condition   WHERE   x      3   OR         x LIKE    3   OR         x LIKE   3    OR         x LIKE    3

User · Answer

An exhaustive or ed where clause can do it without string concatenation   WHERE   x     3  OR         x LIKE    3  OR         x LIKE  3    OR         x LIKE    3        Includes the four cases exact match  end of list  beginning of list  and mid list   This is more verbose  doesn t require the regex extension

User · Answer

A SQLite UDF in PHP PDO for the REGEXP keyword that mimics the behavior in MySQL     pdo- gt sqliteCreateFunction  regexp       function   pattern   data   delimiter         modifiers    isuS                 if  isset  pattern   data      true                        return  preg match sprintf   1 s 2 s 1 s 3 s    delimiter   pattern   modifiers    data   gt  0                      return null             The u modifier is not implemented in MySQL  but I find it useful to have it by default  Examples   SELECT   FROM  table  WHERE  name  REGEXP  sql ite     SELECT   FROM  table  WHERE regexp  sql ite      name         s      If either  data or  pattern is NULL  the result is NULL - just like in MySQL

User · Answer

Consider using this  WHERE x REGEXP        3          This will match exactly 3 when x is in    3 3 12 13 12 13 3 12 3 13   Other examples   WHERE x REGEXP        3 13          This will match on 3 or 13

User · Answer

UPDATE TableName  SET YourField      WHERE YourField REGEXP  YOUR REGEX    And    SELECT   from TableName  WHERE YourField REGEXP  YOUR REGEX

[regex] How do I use regex in a SQLite query?

Examples related to regex

Examples related to sqlite

Examples related to query-string