Книга
Готовимся ко второму этапу тестирования
Функции Transact-SQL для работы со строками и данными типа даты/времени
Функции работы со строками в MS SQL SERVER
Функция LEN
Функция LEN(), концевые пробелы и уникальность

Функция LEN(), концевые пробелы и уникальность

Недавно я столкнулся с тем, что не смог добавить два значения типа VARCHAR, отличающиеся только концевым пробелом в столбец составного первичного ключа (SQL Server 2008). Возможно, этот факт для кого-то является очевидным, но мне показалось странным, что в принципе разные значения считаются дубликатами. Со значениями типа CHAR(n), который имеет фиксированную длину, все понятно, т.к. короткие строки дополняются пробелами до длины n. Поэтому вводимые значения, которые отличаются лишь концевыми пробелами, оказываются неразличимыми. Но концевой пробел в значении типа VARCHAR является как бы обычным символом.

Вот простой эксперимент.

CREATE TABLE Test_Trailing_Space2
(
    num int NOT NULL,
    name VARCHAR(10) NOT NULL,
    PRIMARY KEY(num, name)
);
GO
INSERT INTO Test_Trailing_Space2 VALUES(1, 'John');
INSERT INTO Test_Trailing_Space2 VALUES(1, 'John ');
GO

Вторая строка не будет вставлена в таблицу, при этом будет получено сообщение о нарушении ограничения первичного ключа. Т.е. вторая строка считается дубликатом первой. Может быть дело в том, что при вставке концевой пробел был отсечен. Но нет, вставим другую уникальную строку с концевым пробелом и проверим наличие в ней концевого пробела:

INSERT INTO Test_Trailing_Space2 VALUES(2, 'John ');
GO
SELECT *, LEN(name) len1,DATALENGTH(name) len2
FROM Test_Trailing_Space2;

Результат:

numnamelen1len2
1John44
2John45

Значение в столбце len2 показывает, что пробел присутствует в данных, но, тем не менее, значения ‘John’ и ‘John ’ считаются дубликатами:

SELECT DISTINCT name
FROM Test_Trailing_Space2;
name
John

Очевидно, что все дело в функции LEN(), которая, как известно, не учитывает концевые пробелы. Я не нашел этой информации в документации, но, видимо, именно эта функция используется при сравнении значений любых строковых типов. Мне стало интересно, как ведут себя другие СУБД в этом случае, и я повторил эксперимент для MySQL и PostgreSQL. Были получены следующие результаты.

MySQL (версия 5.0)

SELECT *, OCTET_LENGTH(name) AS len1, LENGTH(name) AS len2
FROM Test_Trailing_Space2;
1John44
2John55
SELECT DISTINCT name FROM Test_Trailing_Space2;
John

PostgreSQL (версия 8.3.6)

SELECT *, OCTET_LENGTH(name) AS len1, LENGTH(name) AS len2
FROM Test_Trailing_Space2;
1“John”44
2“John "55
SELECT DISTINCT name
FROM Test_Trailing_Space2;
“John”
“John "

Как видно, и MySQL, и PostgreSQL учитывают пробел как в числе символов, так и в числе байтов, используемых для хранения строкового значения. При этом MySQL и SQL Server, в отличие от PostgreSQL, считают строки, различающиеся лишь концевыми пробелами, дубликатами. Естественно, PostgreSQL позволяет вставить и такую строку в рассматриваемую таблицу:

INSERT INTO Test_Trailing_Space2
VALUES(1, 'John ');

Вместо выводов

Я далек от того, чтобы высказываться относительно правильности той или иной реализации и, тем более, спорить о том, какая СУБД лучше. Я считаю, что нужно знать досконально ту СУБД, которую вы используете в своей профессиональной деятельности. Изучайте документацию и все подвергайте проверке, не полагаясь на интуицию и «здравый» смысл.

Рекомендуемые упражнения: 131

#LEN