Jak używać wyrażenia regularnego w zapytaniu SQLite?


103

Chciałbym użyć wyrażenia regularnego w sqlite, ale nie wiem jak.

Moja tabela ma kolumnę z ciągami takimi jak ten: "3,12,13,14,19,28,32" Teraz jeśli wpiszę "gdzie x LIKE '3'" Otrzymuję również wiersze zawierające wartości takie jak 13 lub 32 , ale chciałbym uzyskać tylko te wiersze, które mają dokładnie wartość 3 w tym ciągu.

Czy ktoś wie, jak to rozwiązać?


Ta odpowiedź jest najlepsza do dodania funkcji REGEXP do sqlite w c # stackoverflow.com/a/26155359/5734452
hubaishan

Odpowiedzi:


75

SQLite3 obsługuje operator REGEXP:

WHERE x REGEXP <regex>

http://www.sqlite.org/lang_expr.html#regexp


3
Znalazłem łatwy sposób: to po prostu \ bx \ b, gdzie x jest wartością, której należy szukać w ciągu :)
cody

12
@DanS: Jak dodać regex()funkcję wspierającą REGEXPoperatora? Domyślnie funkcja użytkownika nie została dodana.
SK9

47
Zgodnie z dokumentacją Sqlite: Operator REGEXP jest specjalną składnią funkcji użytkownika regexp (). Żadna funkcja użytkownika regexp () nie jest zdefiniowana domyślnie, więc użycie operatora REGEXP zwykle powoduje wyświetlenie komunikatu o błędzie. Jeśli zdefiniowana przez aplikację funkcja SQL o nazwie „regexp” zostanie dodana w czasie wykonywania, funkcja ta zostanie wywołana w celu zaimplementowania operatora REGEXP. ( sqlite.org/lang_expr.html#regexp )
radicand

Dla tych z nas, którzy widzą błąd podczas wypróbowania tego, sprawdź odpowiedź poniżej stackoverflow.com/a/18484596/1585572 Umieściłem kod w pliku i zaimportowałem go do funkcji zdefiniowanych przez użytkownika w moim menedżerze Firefox sqlite. Musisz jednak wywołać to nieco inaczej, na przykład: SELECT * FROM table WHERE column regexp ("myregexp")
Tristan

112

Jak inni już zauważyli, REGEXP wywołuje funkcję zdefiniowaną przez użytkownika, którą należy najpierw zdefiniować i załadować do bazy danych. Może niektóre dystrybucje sqlite lub narzędzia GUI zawierają to domyślnie, ale moja instalacja Ubuntu nie. Rozwiązaniem było

sudo apt-get install sqlite3-pcre

który implementuje wyrażenia regularne Perla w ładowalnym module w /usr/lib/sqlite3/pcre.so

Aby móc z niego korzystać, musisz go ładować za każdym razem, gdy otwierasz bazę danych:

.load /usr/lib/sqlite3/pcre.so

Lub możesz umieścić tę linię w swoim ~/.sqliterc.

Teraz możesz zapytać w ten sposób:

SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';

Jeśli chcesz wykonywać zapytania bezpośrednio z wiersza poleceń, możesz użyć -cmdprzełącznika, aby załadować bibliotekę przed SQL:

sqlite3 "$filename" -cmd ".load /usr/lib/sqlite3/pcre.so" "SELECT fld FROM tbl WHERE fld REGEXP '\b3\b';"

Jeśli korzystasz z systemu Windows, myślę, że podobny plik .dll powinien być gdzieś dostępny.


15
Inna opcja ładowania: utworzyłem widok za pomocą tego: SELECT load_extension ('/ usr / lib / sqlite3 / pcre.so'); W ten sposób, gdy używam punktu wejścia do bazy danych opartego na GUI (jak SQLite Manager w przeglądarce Firefox), mam sposób na załadowanie możliwości REGEXP.
Paulb

30

Sprytnym sposobem rozwiązania tego problemu bez wyrażenia regularnego jest where ',' || x || ',' like '%,3,%'


1
Tak, myślałem o tym, ale za każdym razem nie ma prowadzenia ani podążania za „”. W każdym razie dzięki :-)
cody

Nie natknąłem się tutaj na problem - zastanawiam się, czy to działa, ponieważ x to nazwa kolumny ...
cody

3
Powinieneś użyć',' || x || ','
Baruch

21

SQLite domyślnie nie zawiera funkcji wyrażeń regularnych.

Definiuje REGEXPoperator, ale zakończy się niepowodzeniem z komunikatem o błędzie, chyba że Ty lub Twoja struktura zdefiniujesz funkcję użytkownika o nazwie regexp(). Sposób, w jaki to zrobisz, zależy od Twojej platformy.

Jeśli masz regexp()zdefiniowaną funkcję, możesz dopasować dowolną liczbę całkowitą z listy oddzielonej przecinkami, na przykład:

... WHERE your_column REGEXP "\b" || your_integer || "\b";

Ale naprawdę wygląda na to, że byłoby dużo łatwiej, gdybyś znormalizował strukturę bazy danych, zastępując te grupy w jednej kolumnie oddzielnym wierszem dla każdej liczby na liście oddzielonej przecinkami. Wtedy możesz nie tylko użyć =operatora zamiast wyrażenia regularnego, ale także użyć potężniejszych narzędzi relacyjnych, takich jak sprzężenia, które zapewnia SQL.


14

SQLite UDF w PHP / PDO dla REGEXPsłowa kluczowego, które naśladuje zachowanie w MySQL:

$pdo->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) > 0);
        }

        return null;
    }
);

uModyfikator nie jest zaimplementowany w MySQL, ale uważam, że warto go mieć domyślnie. Przykłady:

SELECT * FROM "table" WHERE "name" REGEXP 'sql(ite)*';
SELECT * FROM "table" WHERE regexp('sql(ite)*', "name", '#', 's');

Jeśli albo $dataalbo $patternjest NULL, wynikiem jest NULL - tak jak w MySQL.


8

moje rozwiązanie w Pythonie z sqlite3:

   import sqlite3
   import re

   def match(expr, item):
        return re.match(expr, item) is not None

   conn = sqlite3.connect(':memory:')
   conn.create_function("MATCHES", 2, match)
   cursor = conn.cursor()
   cursor.execute("SELECT MATCHES('^b', 'busy');")
   print cursor.fetchone()[0]

   cursor.close()
   conn.close()

jeśli wyrażenie regularne jest zgodne, wynikiem będzie 1, w przeciwnym razie 0.


5

Nie, dobrze jest odpowiedzieć na pytanie, które pojawiło się prawie rok temu. Ale piszę to dla tych, którzy myślą, że Sqlite sam zapewnia funkcję REGEXP .

Jednym z podstawowych wymagań do wywołania funkcji REGEXP w sqlite jest
„Należy utworzyć własną funkcję w aplikacji, a następnie podać łącze wywołania zwrotnego do sterownika sqlite” .
W tym celu musisz użyć sqlite_create_function (interfejs C). Szczegóły można znaleźć tutaj i tutaj


4
UPDATE TableName
 SET YourField = ''
WHERE YourField REGEXP 'YOUR REGEX'

I :

SELECT * from TableName
 WHERE YourField REGEXP 'YOUR REGEX'

4

Wyczerpująca klauzula or'ed where może to zrobić bez konkatenacji ciągów:

WHERE ( x == '3' OR
        x LIKE '%,3' OR
        x LIKE '3,%' OR
        x LIKE '%,3,%');

Obejmuje dokładne dopasowanie czterech przypadków, koniec listy, początek listy i środek listy.

Jest to bardziej szczegółowe, nie wymaga rozszerzenia wyrażenia regularnego.


4

W Pythonie, zakładając conpołączenie z SQLite, możesz zdefiniować wymagany UDF pisząc:

con.create_function('regexp', 2, lambda x, y: 1 if re.search(x,y) else 0)

Oto bardziej kompletny przykład:

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' ")


Sprawdzenie IMHO powinno być if x not Null and y not Null and re.search(x,y)inaczej to wyrzuci.
pholat

2

Możesz użyć wyrażenia regularnego z REGEXP , ale jest to głupi sposób na dokładne dopasowanie.

Powinieneś po prostu powiedzieć WHERE x = '3'.


Powinienem był to lepiej wyjaśnić (przepraszam za mój słaby angielski), miałem na myśli tylko określoną dokładną wartość, a nie dokładny ciąg. W każdym razie dzięki!
cody

2

Rozważ użycie tego

WHERE x REGEXP '(^|,)(3)(,|$)'

Dopasuje dokładnie 3, gdy x jest w:

  • 3
  • 3,12,13
  • 12,13,3
  • 12,3,13

Inne przykłady:

WHERE x REGEXP '(^|,)(3|13)(,|$)'

To będzie pasować w 3 lub 13


1

W przypadku, gdy ktoś szuka warunku innego niż wyrażenie regularne dla systemu Android Sqlite , takiego jak ten ciąg [1,2,3,4,5], nie zapomnij dodać nawiasu ( [] ) tego samego dla innych znaków specjalnych, takich jak nawias ( {} ) w warunku @phyatt

WHERE ( x == '[3]' OR
        x LIKE '%,3]' OR
        x LIKE '[3,%' OR
        x LIKE '%,3,%');


0

Możesz również rozważyć

WHERE x REGEXP '(^|\D{1})3(\D{1}|$)'

Pozwoli to znaleźć numer 3 w dowolnym łańcuchu na dowolnej pozycji


0

U Julii model do naśladowania można zilustrować następująco:

using SQLite
using DataFrames

db = SQLite.DB("<name>.db")

register(db, SQLite.regexp, nargs=2, name="regexp")

SQLite.Query(db, "SELECT * FROM test WHERE name REGEXP '^h';") |> DataFrame

0

do szyn

            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
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.