Jak wyszukiwać (bez rozróżniania wielkości liter) w kolumnie za pomocą symboli LIKE?


289

Rozejrzałem się dookoła i nie znalazłem, o co mi chodzi, więc proszę.

SELECT * FROM trees WHERE trees.`title` LIKE  '%elm%'

Działa to dobrze, ale nie, jeśli drzewo nosi nazwę Elm lub ELM itp.

Jak sprawić, by SQL nie rozróżniał wielkości liter w tym wyszukiwaniu symboli wieloznacznych?

Używam MySQL 5 i Apache.


3
Jeśli natknąłeś się na tę stronę, ale twoje ustawienia MySql działają na tym zapytaniu dla Elm lub ELM i CHCESZ rozróżniać małe i wielkie litery, zobacz BINARYtakie jak tutaj: stackoverflow.com/questions/7857669/…
joshuahedlund

Czy to nie zależy od sortowania pól / tabel? jak „ut8_general_CI”
Yousha Aleayoub

Jak go użyć do sekwencjonowania zapytania o węzeł?
Ashh,

MySQL likepowinien domyślnie nie uwzględniać wielkości liter.
Marko Bonaci

Odpowiedzi:


281
SELECT  *
FROM    trees
WHERE   trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'

W rzeczywistości, jeśli dodasz COLLATE UTF8_GENERAL_CIdo definicji kolumny, możesz po prostu pominąć wszystkie te sztuczki: zadziała to automatycznie.

ALTER TABLE trees 
 MODIFY COLUMN title VARCHAR(…) CHARACTER 
 SET UTF8 COLLATE UTF8_GENERAL_CI. 

Spowoduje to również przebudowanie wszystkich indeksów w tej kolumnie, dzięki czemu będą mogły być używane do zapytań bez wiodącego „%”


34
W rzeczywistości, jeśli dodasz COLLATE UTF8_GENERAL_CIdo definicji kolumny, możesz po prostu pominąć wszystkie te sztuczki: zadziała to automatycznie. ALTER TABLE trees MODIFY COLUMN title VARCHAR(…) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI. Spowoduje to również przebudowanie wszystkich indeksów w tej kolumnie, dzięki czemu będą mogły być używane do zapytań bez wiodącego „%”.
Quassnoi

1
ALTER TABLE drzewa MODYFIKUJ KOLUMNĘ tytuł VARCHAR (…) wydaje się to najlepszy sposób, dzięki wielkie ... niech sql wykona pracę
David Morrow

10
Przyjazne przypomnienie, że jest to odpowiedź mysql. Jeśli używasz PostgreSQL, ILike jest rozwiązaniem na powyższe pytanie.
Steve

2
@RajanRawal: zamieść swoje pytanie jako pytanie, a nie komentarz. Dzięki.
Quassnoi,

1
To poprawna odpowiedź, o ile oryginalny zestaw był ogólny, a nie bin.
greenoldman

261

Zawsze rozwiązałem to, używając niższej:

SELECT * FROM trees WHERE LOWER( trees.title ) LIKE  '%elm%'

47
i wykorzystanie indeksu zepsucia
Your Common Sense

3
Czy MySQL 5 ma operatora ILIKE?
Luke Maurer

27
choć dla wyszukiwania %% to i tak nie ma znaczenia :)
Twój zdrowy rozsądek

5
@Przełęcz. - Wprawdzie nie jest to idealne rozwiązanie dla indeksowanych kolumn, ale będzie działać w przypadku struktury, która już istnieje. Odkryłem również, że wyszukiwania bez rozróżniania wielkości liter częściej występują w kolumnach, które i tak nie są indeksowane.
cwallenpoole,

1
Domyślnym zestawieniem jest już CI. Prawdziwy problem nie dotyczy tego konkretnego pytania. Ale wciąż jest to idealna odpowiedź w stylu SO.
Twój zdrowy rozsądek

48

Rozróżnianie wielkości liter jest określone w ustawieniach sortowania kolumn / tabel / bazy danych. Możesz wykonać zapytanie w ramach określonego sortowania w następujący sposób:

SELECT *
FROM trees
WHERE trees.`title` LIKE '%elm%' COLLATE utf8_general_ci

na przykład.

(Zamień na utf8_general_cidowolne zestawienie, które uznasz za przydatne). _ciOznacza wielkość liter ma znaczenie .


1
W MySQL 5.6 otrzymuję ERROR 1273 (HY000): Nieznane sortowanie: 'utf_general_ci' . Zgaduję, że to sortowanie zostało usunięte z MySQL? utf8_general_cijednak działa dobrze.
Mark Amery

1
Miałem ten sam problem. Musisz albo naprawić swój, COLLATEalbo wykonać prostą sztuczkę, taką jak ta ( LOWER()obie struny przed porównaniem)
Menelaos Kotsollaris,

W MySQL 5.6+ lub MariaDB 10+ wystarczy dostarczyć instrukcję COLLATE przed spełnieniem warunku. Więc to działa:SELECT * FROM products WHERE name COLLATE utf8_general_ci LIKE 'AB47TU';
stamster

41

Oto przykład prostej kwerendy LIKE:

SELECT * FROM <table> WHERE <key> LIKE '%<searchpattern>%'

Teraz rozróżniana jest wielkość liter przy użyciu funkcji LOWER ():

SELECT * FROM <table> WHERE LOWER(<key>) LIKE LOWER('%<searchpattern>%')

3
W rzeczywistości jest to całkiem fajne rozwiązanie, zwłaszcza gdy masz do czynienia z COLLATEproblemami z formatem
Menelaos Kotsollaris,

27

Po prostu użyj:

"SELECT * FROM `trees` WHERE LOWER(trees.`title`) LIKE  '%elm%'";

Albo użyj

"SELECT * FROM `trees` WHERE LCASE(trees.`title`) LIKE  '%elm%'";

Obie funkcje działają tak samo


15

Robię coś takiego.

Pobieranie wartości małymi literami i MySQL zajmuje się resztą

    $string = $_GET['string'];
    mysqli_query($con,"SELECT *
                       FROM table_name
                       WHERE LOWER(column_name)
                       LIKE LOWER('%$string%')");

I dla MySQL PDO Alternative:

        $string = $_GET['string'];
        $q = "SELECT *
              FROM table_name
              WHERE LOWER(column_name)
              LIKE LOWER(?);";
        $query = $dbConnection->prepare($q);
        $query->bindValue(1, "%$string%", PDO::PARAM_STR);
        $query->execute();

6

Myślę, że to zapytanie spowoduje wyszukiwanie bez rozróżniania wielkości liter:

SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';

1
Dostaję błąd składniowy na mysql 5.5 podczas korzystania z ILIKE w moich zapytaniach
Steel Brain

18
Działa to tylko w przypadku PostgreSQL; nie MySQL. postgresql.org/docs/current/static/functions-matching.html
Martin Tournoij

Jak już wspomniano, pytanie dotyczyło MySQL, a odpowiedź dotyczy PostgreSQL i na pewno nie działa z MySQL. Nie głosuję w dół, ale nie mogę się zastanawiać, skąd pochodzą głosy w górę ...
silverdr 24.01.2018

5

posługiwać się ILIKE

SELECT * FROM trees WHERE trees.`title` ILIKE '%elm%';

zadziałało dla mnie !!


9
MySQL nie obsługuje ILIKE.
ttarchala,

3
To działa dla PostgreSQL, pytanie dotyczyło MySQL.
Telmo Trooper

4

Nie potrzebujesz ALTERżadnego stołu. Po prostu skorzystaj z następujących zapytań, przed faktycznym SELECTzapytaniem, którego chcesz użyć z użyciem symbolu wieloznacznego:

    set names `utf8`;
    SET COLLATION_CONNECTION=utf8_general_ci;
    SET CHARACTER_SET_CLIENT=utf8;
    SET CHARACTER_SET_RESULTS=utf8;

2
To bardzo niedoceniany komentarz. Najogólniej odnosi się do pytania. Myślę, że składnia tabeli zmian jest również ważna, ponieważ pytanie może chcieć ograniczyć porównanie tylko do jednej kolumny.
Brian Chrisman,

1

dobrze w mysql 5.5, podobnie jak operator jest niewrażliwy ... więc jeśli twoją doliną jest wiąz, ELM lub Wiąz lub eLM lub jakikolwiek inny i użyjesz np. '% elm%', wyświetli wszystkie pasujące wartości.

Nie mogę powiedzieć o wcześniejszych wersjach mysql.

Jeśli korzystasz z Oracle, na przykład pracuj z rozróżnianiem wielkości liter, więc jeśli wpiszesz „% elm%”, przejdzie tylko do tego i zignoruje wielkie litery.

Dziwne, ale tak to jest :)


To nie do końca prawda. Działa w ten sposób tylko wtedy, gdy ustawione jest sortowanie *_ci, co oznacza „bez uwzględniania wielkości liter”. Ponieważ zdarza się, że jest to domyślne dla wszystkich obsługiwanych zestawów znaków (problem show character set;to sprawdzić) - odpowiedź jest częściowo zgodna z prawdą :-) Tylko przyczyna jest nieprawidłowa. To nie operator rozróżnia małe i wielkie litery, to domyślne sortowanie.
silverdr

tak, zgadzam się z tobą. Zależy to od postaci, a jeśli nadal jesteś w produkcji ze znakiem * _ci, wówczas jedyną opcją jest użycie
pliku

1
SELECT name 
       FROM gallery 
       WHERE CONVERT(name USING utf8) LIKE _utf8 '%$q%' 
       GROUP BY name COLLATE utf8_general_ci LIMIT 5 

galeria to nazwa tabeli, nazwa to kolumna w tabeli,
user4189641

2
dodaj wyjaśnienie kodu pokazujące, co robi i jak pomaga - pomoże to innym w przyszłości
Our Man in Bananas

0

Musisz skonfigurować odpowiednie kodowanie i zestawianie tabel.

Kodowanie w tabeli musi odzwierciedlać faktyczne kodowanie danych. Jakie jest twoje kodowanie danych?

Aby zobaczyć kodowanie tabeli, możesz uruchomić zapytanie SHOW CREATE TABLE tablename


0

Kiedy chcę opracować niewrażliwe wyszukiwanie wielkości liter, zawsze dokonuję porównania każdego łańcucha na małe litery


0

Możesz użyć następującej metody

 private function generateStringCondition($value = '',$field = '', $operator = '', $regex = '', $wildcardStart = '', $wildcardEnd = ''){
        if($value != ''){
            $where = " $field $regex '$wildcardStart".strtolower($value)."$wildcardEnd' ";

            $searchArray = explode(' ', $value);

            if(sizeof($searchArray) > 1){

                foreach ($searchArray as $key=>$value){
                    $where .="$operator $field $regex '$wildcardStart".strtolower($value)."$wildcardEnd'";
                }

            }

        }else{
            $where = '';
        }
        return $where;
    }

użyj tej metody jak poniżej

   $where =  $this->generateStringCondition($yourSearchString,  'LOWER(columnName)','or', 'like',  '%', '%');
  $sql = "select * from table $where";

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.