Регистрозависимый поиск для MySQL. Функции LOWER и UPPER

Если Вы уже давно работаете с ПО MySQL , то обратили внимание, что любая выборка, либо поиск идёт без учёта регистра . В большинстве случаев, это удобно, однако, иногда всё-таки бывает нужно сделать регистрозависимый поиск для MySQL . Вот об этом я сегодня и напишу.

Чтобы стало понятно, о чём идёт речь, давайте сразу приведу пример SQL-запроса :

SELECT * FROM `users` WHERE `login`="admin"

Данный запрос вернёт пользователя с логином "admin ". Однако, всё не совсем не так. Если будет пользователь "Admin " (или, например, "AdMiNM "), то он также будет результирующим.

На практике такой ошибки не будет. Если Вы всё правильно сделали (хотя бы поставили UNIQUE на соответсвующее поле), то такие значения как "admin " и "Admin " никогда вместе не будут. Поэтому в данном случае, регистрозависимая выборка вовсе не нужна.

А вот разберём другой пример. Допустим, у Вас в базе хранятся открытые пароли (это очень плохо, но допустим), и Вы хотите провести авторизацию пользователя. Предположим, что у пользователя "admin " пароль "myPassWord ". Однако, пользователь вводит такой пароль: "mypassword ". В результате получится такой SQL-запрос :

SELECT * FROM `users` WHERE `login`="admin" AND `password`="mypassword"

Так как регистр по умолчанию не учитывается, следовательно, такой пользователь будет найден и пароль совпадёт, чего быть не должно, так как если регистр логина не важен, то вот регистр пароля обязательно надо учитывать.

Проблема решается с помощью BINARY . Если указать данный оператор в запросе, то следующие за ним строки будут регистрозависимые. Это работает по следующей причине: все дальнейшие строки после BINARY будут переведены в двоичный код. А код для верхнего и нижнего регистра различается. Следовательно, операция сравнения, если строки не совпадают с точностью до регистра, вернёт false , поскольку их двоичный код различен. Для нашего с Вами примера вот этот запрос будет верным:

SELECT * FROM `users` WHERE `login`="admin" AND BINARY `password`="mypassword"

Вот теперь, если пароль будет введён без учёта регистра, то будет ошибка авторизации. Как можно увидеть, BINARY стоит перед паролем, следовательно, в логине регистр учитываться не будет, а в пароле регистр будет учтён. Именно так и должно быть.

Вот таким несложным способом делается регистрозависимые поиск и выборка для MySQL .

Функции LOWER и UPPER позволяют быстро и просто изменить регистр строки, чтобы символы отображались в нижнем (LOWER) или верхнем (UPPER) регистре. Эти функции поддерживаются во всех реализациях, описываемых в этой книге. Разные платформы, кроме того, поддерживают различные другие функции для форматирования текста, которые являются специфичными для конкретной реализации.

Синтаксис SQL 2003

LOWER(строка) UPPER(строка)

Функция LOWER преобразует строку в нижний регистр, а функция UPPER, наоборот, в верхний.

DB2 и MySQL

Эти платформы поддерживают скалярные функции LOWER и UPPER стандарта SQL 2003, а также их синонимы UCASE и LCASE.

Oracle, PostgreSQL и SQL Server

Эти платформы поддерживают скалярные функции LOWER и UPPER стандарта SQL 2003, и это показано в приведенном ниже примере.

SELECT LOWER("You Talkin To ME?"), UPPER("you talking to me");

you talkin to me?

YOU TALKING TO ME?!

OVERLAY

Функция ОVERLAY вставляет одну строку в другую и возвращает результат.

Синтаксис SQL 2003

OVERLAY(строка PLACING встраиваемая_строка FROM начало )

Если хотя бы один из входных параметров будет равен NULL, функция возвращает NULL, встраиваемая_строка заменяет количество символов, указываемое параметром длина, в указанной строке, начиная с начала. Если длина не указана, то встраиваемая строка заменит все символы, идущие в строке после начала.

DB2, MySQL, Oracle и SQL Server

Эти платформы не поддерживают функцию OVERLAY. Вы можете сымитировать действие этой функции на этих платформах при помощи комбинации функции SUBSTRING и оператора конкатенации.

PostgreSQL

Платформа PostgreSQL поддерживает для функции О VERLA Y стандарт ANSI. Примеры

Ниже приводится пример использования функции OVERLAY.

SQL 2003 и PostgreSQL

SELECT OVERLAY("DONALD DUCK" PLACING "TRUMP" FROM 8) FROM NAMES;

SUBSTRING

Функция SUBSTRING позволяет извлечь одну строку из другой.

Синтаксис SQL 2003

SUBSTRING{строка_для_извлечения FROM начало )

Если хотя бы один из входных параметров равен NULL, функция SUBSTRING возвращает NULL. Параметр строка_для_экстращии - это исходная строка, откуда нужно извлечь символьное значение. Это может быть строковая константа, столбец таблицы, относящийся к символьному типу, или переменная символьного типа. Параметр начало - это целочисленное значение, указывающее, с какой позиции нужно начинать извлечение. Необязательный параметр длина - это целочисленное значение, указывающее, сколько символов нужно извлечь, начиная с начала. Если дополнительное ключевое слово FOR опущено, то подстрока будет начинаться с начала и продолжаться до конца строки_для_извлечения.

DB2

SUBSTRING{строка_для_извлечения, начало [, длина])

MySQL

SUBSTRING(строка_для_извлечения FROM начало)

Реализация данной функции в MySQL подразумевает, что символы будут извлекаться с начала и извлечение будет продолжаться до конца строки.

Oracle

SUBSTRING(строка_для_извлечения, начало [, длина])

Реализация данной функции в DB2, под названием SUBSTR, по большей части функционально эквивалентна стандартной функции SUBSTRING. Предложение COLLATE не поддерживается. Если параметр начало представляет собой отрицательное число, Oracle отсчитывает символы от конца строки_для_извлечения. Если параметр длина опущен, извлекается оставшаяся часть строки (от начала).

PostgreSQL

SUBSTRING{строка_для_извлвчения [Р0Н длина])

Платформа PostgreSQL в основном поддерживает стандарт ANSI, за исключением того, что не поддерживается предложение COLLATE.

SQL Server

SUBSTRING{строка_для_извлечения [Р0Н длина])

Платформа SQL Server в основном поддерживает стандарт ANSI, за исключением того, что не поддерживается предложение COLLATE. SQL Server позволяет использовать эту команду применительно к тексту, изображениям и двоичным типам данных, однако параметры начало и длина показывают здесь не количество символов, а количество байт.

Эти примеры, как правило, будут работать на всех пяти описываемых в этой книге платформах. Только второй пример для Oracle, с отрицательным параметром начало, не будет выполняться на других платформах (конечно, нужно перевести ключевое слово Oracle SUBSTR в SUBSTRING).

/* Для Oracle, подсчет начинается слева */ SELECT SUBSTR("ABCDEFG", 3.4) FROM DUAL; Результат: "CDEF" /* Для Oracle, подсчет начинается справа */ SELECT SUBSTR("ABCDEFG", -5.4) FROM DUAL; Результат: "CDEF" /* Для MySQL */ SELECT SUBSTRI, NG("Be vewy, vewy quiet") FROM 5 Результат: "wy, vewy quiet" /* Для PostgreSQL или SQL Server */ SELECT aujname.SUBSTRING(au_fname, 1. 1) FROM authors WHERE au_lname="Carson" Carson С

TRIM

Функция TRIM удаляет из указанной символьной строки или значения типа BLOB первые символы, последние символы или и те и другие сразу. Также эта функция удаляет из указанной символьной строки и другие типы символов. По умолчанию функция удаляет указанный символ с обеих сторон символьной строки. Если удаляемый символ не указан, по умолчанию функция удаляет пробелы.

SELECT TRIM(" wamalamadingdong "); » wamalamadingdong; SELECT LTRIM(RTRIM(" wamalamadingdong ")); » wamalamadingdong SELECT TRIM(LEADING "19" FROM "1976 AMC GREMLIN"); » 76 AMC GREMLIN SELECT TRIM(BOTH "x" FROM "xxxWHISKEYxxx"); » WHISKEY SELECT TRIM(TRAILING "snack" FROM "scooby snack"); » scooby

SQL Server проверяет чувствительность к регистру? (5)

Как проверить, работает ли база данных в SQL Server с учетом регистра? Я ранее выполнял запрос:

SELECT CASE WHEN "A" = "a" THEN "NOT CASE SENSITIVE" ELSE "CASE SENSITIVE" END

Но я ищу другие способы, поскольку это фактически дало мне проблемы в прошлом.

Изменить - немного больше информации. В существующем продукте есть много предварительно написанных хранимых процедур. В хранимой процедуре @test != @TEST зависимости от чувствительности самого сервера. Так что я ищу, это лучший способ проверить сервер на его чувствительность.

Как проверить, работает ли база данных в SQL Server с учетом регистра?

Вы можете использовать нижеприведенный запрос, который возвращает вашу информированную базу данных, чувствителен к регистру или нет или находится в двоичной сортировке (с нулевым результатом):

;WITH collations AS (SELECT name, CASE WHEN description like "%case-insensitive%" THEN 0 WHEN description like "%case-sensitive%" THEN 1 END isCaseSensitive FROM sys.fn_helpcollations()) SELECT * FROM collations WHERE name = CONVERT(varchar, DATABASEPROPERTYEX("yourDatabaseName","collation"));

SQL Server не чувствителен к регистру. SELECT * FROM SomeTable - это то же, что SeLeCT * frOM soMetaBLe .

Вы заинтересованы в сортировке. Вы можете создать что-то на основе этого фрагмента:

SELECT DATABASEPROPERTYEX("master", "Collation");

Обновить
На основе вашего редактирования. Если @test и @TEST могут ссылаться на две разные переменные, это не SQL Server. Если вы видите проблемы, когда одна и та же переменная не равна самому себе, проверьте, равна ли эта переменная NULL , потому что NULL = NULL возвращает `false.

Если вы установили SQL Server с параметрами сопоставления по умолчанию, вы можете обнаружить, что следующие запросы возвращают те же результаты:

CREATE TABLE mytable (mycolumn VARCHAR(10)) GO SET NOCOUNT ON INSERT mytable VALUES("Case") GO SELECT mycolumn FROM mytable WHERE mycolumn="Case" SELECT mycolumn FROM mytable WHERE mycolumn="caSE" SELECT mycolumn FROM mytable WHERE mycolumn="case"

Вы можете изменить свой запрос, вызывая сопоставление на уровне столбца:

SELECT myColumn FROM myTable WHERE myColumn COLLATE Latin1_General_CS_AS = "caSE" SELECT myColumn FROM myTable WHERE myColumn COLLATE Latin1_General_CS_AS = "case" SELECT myColumn FROM myTable WHERE myColumn COLLATE Latin1_General_CS_AS = "Case" -- if myColumn has an index, you will likely benefit by adding -- AND myColumn = "case" SELECT DATABASEPROPERTYEX("", "Collation")

Поскольку изменение этого параметра может повлиять на приложения и SQL-запросы, я бы сначала изолировал этот тест. Из SQL Server 2000 вы можете легко запустить оператор ALTER TABLE, чтобы изменить порядок сортировки определенного столбца, заставляя его учитывать регистр. Сначала выполните следующий запрос, чтобы определить, что вам нужно, чтобы изменить его:

EXEC sp_help "mytable"

Второй набор записей должен содержать следующую информацию в сценарии по умолчанию:

Column_Name Collation

mycolumn SQL_Latin1_General_CP1_CI_AS

Независимо от столбца «Collation», вы теперь знаете, что вам нужно, чтобы изменить его обратно после внесения следующего изменения, которое заставит чувствительность к регистру:

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(10) COLLATE Latin1_General_CS_AS GO SELECT mycolumn FROM mytable WHERE mycolumn="Case" SELECT mycolumn FROM mytable WHERE mycolumn="caSE" SELECT mycolumn FROM mytable WHERE mycolumn="case"

Если это заглочит, вы можете изменить его обратно, просто выпустив новый оператор ALTER TABLE (обязательно замените мой идентификатор COLLATE на тот, который вы нашли ранее):

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS

Если вы застряли с SQL Server 7.0, вы можете попробовать это обходное решение, которое может быть немного больше связано с производительностью (вы должны получить результат только для ПЕРВОГО матча):

SELECT mycolumn FROM mytable WHERE mycolumn = "case" AND CAST(mycolumn AS VARBINARY(10)) = CAST("Case" AS VARBINARY(10)) SELECT mycolumn FROM mytable WHERE mycolumn = "case" AND CAST(mycolumn AS VARBINARY(10)) = CAST("caSE" AS VARBINARY(10)) SELECT mycolumn FROM mytable WHERE mycolumn = "case" AND CAST(mycolumn AS VARBINARY(10)) = CAST("case" AS VARBINARY(10)) -- if myColumn has an index, you will likely benefit by adding -- AND myColumn = "case"

Сортировка может быть установлена на разных уровнях:

сервер
База данных
колонка

Таким образом, у вас может быть столбец с чувствительностью к регистру в базе данных, нечувствительной к регистру. Я еще не столкнулся с ситуацией, когда можно было бы рассмотреть деловое дело для чувствительности к регистру одного столбца данных, но я полагаю, что может быть.

Проверить сортировку сервера

SELECT SERVERPROPERTY("COLLATION")

Проверка сопоставления базы данных

SELECT DATABASEPROPERTYEX("AdventureWorks", "Collation") SQLCollation;

Проверка сортировки столбцов

Select table_name, column_name, collation_name from INFORMATION_SCHEMA.COLUMNS where table_name = @table_name