How do I use regex in a SQLite query?

by

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.

More Related Contents:

  1. How can I match query string variables with mod_rewrite?
  2. replace a part of a string with REGEXP in sqlite3
  3. Regular expression to remove one parameter from query string
  4. Phone number regular expressions exact format
  5. RegEx: Grabbing values between quotation marks
  6. Regex to match string containing two names in any order
  7. Regular Expression to match valid dates
  8. Validating email addresses using jQuery and regex
  9. Extract a regular expression match
  10. Regular Expression to match only alphabetic characters
  11. Regular expression that doesn’t contain certain string [duplicate]
  12. Capture value out of query string with regex?
  13. Returning a regex match in VBA (excel)
  14. Regex to match month name followed by year
  15. Regex for alphanumeric, but at least one letter
  16. regex to match word boundary beginning with special characters
  17. Does the ‘o’ modifier for Perl regular expressions still provide any benefit?
  18. regular expression to match everything until the last occurrence of /
  19. What regular expression can never match?
  20. Regular Expression Validation For Indian Phone Number and Mobile number
  21. How do I perform a Perl substitution on a string while keeping the original?
  22. replace a unknown string between two known strings with sed
  23. How can I parse quoted CSV in Perl with a regex?
  24. htaccess force https and redirect www to non-www, but no other subdomains
  25. How to escape dollar sign ($) in a string using perl regex
  26. Checking for diacritics with a regular expression
  27. Filtering a diff with a regular expression
  28. Remove all characters before a period in a string
  29. powershell extract text between two strings
  30. Get Twitter @Username with Regex in R

Leave a Comment