Приветствую коллеги, сегодня я бы хотел поделиться небольшой заметкой и случаем из практики. О том, как можно найти невидимые символы в таблицах SQL Server с помощью функции CHARINDEX.
Подобная ситуация случается не так часто, однако способ решения проблемы который мы рассмотрим сегодня может быть очень полезным и сэкономить вам время в случае когда в ваших данных хранятся неожиданные символы и они оказывают влияние на ваше приложение.
Такая ситуация может возникать в случаях недостаточной или отсутствующей фильтрации на этапе добавления данных в базы.
К сути. Недавно у меня произошла ситуация при которой в таблице из десятков тысяч строк на одной строке в поле с типом nvarchar был записан невидимый символ Unit Separator (0x001F). Визуально проблемная ячейка выглядела как и другие, т.к этот символ не отображался в SQL Management Studio или других sql клиентах.
Этот символ оказывал влияние на фоновые обработчики которые запрашивали данные из этих таблиц и представляли их для другой информационной системы. Так я и узнал о возникшей проблеме, т.к в журналах обработчиков сообщалась ошибка:
Msg 6841, Level 16, State 1, Line 1
FOR XML could not serialize the data for node ‘ColumnName’ because it contains a character (0x001F) which is not allowed in XML.
Одним из вариантов поиска строк с некорректными данными является использование функции в SQL Server CHARINDEX.
Функция выполняет поиск символа или подстроки в строке возвращая позицию первого вхождения. В случае отсутствия результата поиска функция возвращает 0. Функция CHARINDEX получает на вход несколько параметров (что мы ищем, где мы ищем, (необязательно)начальная позиция поиска).
Давайте попробуем поискать невидимый символ в наших данных. Для этого смоделируем проблемную ситуацию и создадим тестовую таблицу содержащую невидимый символ. Для этого выполним запросы:
CREATE TABLE testTable( [Id] [int] NOT NULL, [ColumnName] [nvarchar](50) NULL ) INSERT INTO testTable VALUES (1, 11) INSERT INTO testTable VALUES (2, 22) INSERT INTO testTable VALUES (3, 33) INSERT INTO testTable VALUES (4, 44) INSERT INTO testTable VALUES (5, 55) INSERT INTO testTable VALUES (6, 66) INSERT INTO testTable VALUES (7, concat(7, nchar(0x001F), 7)) INSERT INTO testTable VALUES (8, 88) INSERT INTO testTable VALUES (9, 99) SELECT * FROM testTable
Мы с вами видим, что визуально данные выглядят корректно, хотя в строке с Id = 7 находится специальный символ.
Теперь давайте попробуем найти проблемную строку. Для этого выполним запрос:
SELECT * FROM testTable WHERE charindex(nchar(0x001F), ColumnName) <> 0
Запрос вернет нам строки содержащие невидимый символ и после этого мы сможем изменить некорректные данные.