Replace ms sql: REPLACE (Transact-SQL) — SQL Server

Поиск информации в тексте средствами MS SQL Server — Разработка на vc.ru

В своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В excel для этого можно использовать текстовые формулы и регулярные выражения VBA. Кроме того, регулярные выражения и текстовые функции можно использовать в python и других языках программирования.

2724
просмотров

Но как быть, если объем информации измеряется миллионами записей в базе данных, а времени на выгрузку и анализ небольших порций данных нет? Использование Transact-SQL (который хоть и не поддерживает регулярные выражения в полной мере, имеет более расширенный функционал оператора like, чем стандартный SQL) позволит переложить вычисления на плечи СУБД и сэкономить время на копировании данных.

Для примера выгрузили отзывы клиентов со страницы www.banki.ru/services/responses/bank/sberbank/ и импортировали данные в MS SQL Server. 0-9]%’
and [Номер телефона] not like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’
)
if @t like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%’
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-12)
where [Номер телефона] [email protected]
end
else
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-1)
where [Номер телефона] [email protected]
end
end
go
update [dbo].[banki_ru_messages]
set [Номер телефона]=left([Номер телефона], 11)
where [Номер телефона] is not null
go
alter table [dbo].[banki_ru_messages]
alter column [Номер телефона] nvarchar(11)
go
select * from [dbo].[banki_ru_messages]
where [Номер телефона] is not null

Аналогичным способом, изменив количество цифр, можно искать номера счетов, ИНН, почтовые индексы.

Для поиска адресов электронной почты можно воспользоваться следующим кодом:

alter table [dbo]. [banki_ru_messages]
add [E-mail] nvarchar(max)
go
update [dbo].[banki_ru_messages]
set [E-mail]=[Сообщение]
where [Сообщение] like ‘%[A-z0-9]@[A-z0-9]%’
go
declare @t nvarchar(max)
while exists (select top 1 [E-mail] from [dbo].[banki_ru_messages]
where [E-mail] is not null and charindex(‘ ‘, [E-mail])>0)
begin
set @t=(select top 1 [E-mail] from [dbo].[banki_ru_messages]
where [E-mail] is not null and charindex(‘ ‘, [E-mail])>0)
if left(@t, charindex(‘ ‘, @t)-1) like ‘%[A-z0-9]@[A-z0-9]%’
begin
update [dbo].[banki_ru_messages]
set [E-mail]=left(@t, charindex(‘ ‘, @t)-1)
where [E-mail][email protected]
end
else
begin
update [dbo].[banki_ru_messages]
set [E-mail]=right(@t, len(@t)-charindex(‘ ‘, @t)+1)
where [E-mail][email protected]
end
end
go
select * from [dbo].[banki_ru_messages]
where [E-mail] is not null or [ИНН] is not null or [Номер телефона] is not null

Описанные алгоритмы конечно же имеют погрешность (количество подряд идущих цифр не всегда однозначно определяет их суть, да и символ «@» встречается не только в адресах электронной почты), но в определенных ситуациях позволяют сэкономить время и дать представление о наличии и объеме искомой информации в текстовых полях.

Использование функции SQL REPLACE и примеры

Автор: Daniel Calbimonte

Функция REPLACE SQL используется для замены строки или подстроки строки
с другой строкой в
T-SQL
сценарий,

оператор ВЫБОР,
ОБНОВЛЯТЬ
оператор, запрос SQL или

хранимая процедура в базе данных Microsoft SQL.

Синтаксис 

 REPLACE(выражение, stringToReplace, stringReplacement)
Параметры
  • выражение — Исходная строка или
    выражение, которое нужно заменить полностью или частично, это может быть varchar, nvarchar
    или двоичные типы данных.
  • stringToReplace — Заменяемое строковое значение, можно
    быть varchar, nvarchar или двоичными типами данных.
  • stringReplacement — Замена строки, может
    быть varchar, nvarchar или двоичными типами данных.

Пример простой функции SQL REPLACE

В следующем примере слово «Мир» заменяется на «MSSQLTIPS».
создать новую строку.

 SELECT REPLACE('Hello World','World','MSSQLTIPS') в качестве вывода

Значения NULL в функции SQL REPLACE

Если некоторые из аргументов имеют значение NULL, выходные данные будут равны NULL. Чтобы заменить значения NULL,
вместо этого вам нужно будет использовать функцию ISNULL.

 SELECT REPLACE(NULL,NULL,'None') в качестве вывода

Удаление слова с помощью функции SQL REPLACE

В следующем примере будет удалено слово «World» из «Hello World».

 SELECT REPLACE('Hello World','World','') в качестве вывода

Использовать SQL REPLACE с числовыми значениями

В следующем примере показано, что с помощью этой функции можно заменить числовые числа.
В этом примере цифра 1 заменена на 5.

 ВЫБЕРИТЕ ЗАМЕНУ (11112233,1,5) в качестве вывода

Работа с параметрами сортировки и функцией SQL REPLACE

В следующем примере слово «Мир» заменяется коллекцией «Albanan_BIN».
сопоставления и заменяется пустой строкой.

 SELECT REPLACE('Hello World' COLLATEAlbanan_BIN ,'World','') в качестве вывода

Чтобы получить список доступных сопоставлений (с учетом регистра и без учета регистра), используйте следующий запрос.

 ВЫБЕРИТЕ Имя, Описание ИЗ fn_helpcollations()

Использование функции SQL REPLACE с таблицей и столбцом

В следующем примере открывающая скобка заменяется дефисом, а
закрыть скобку дефисом. Итак, мы вкладываем 2 вызова REPLACE вместе следующим образом.

 SELECT REPLACE(REPLACE(PhoneNumber,'(','-'), ')','-') как замененный, PhoneNumber
ОТ [Person].[PersonPhone]

Использование функции SQL REPLACE для замены нескольких пробелов одним пробелом

В этом примере мы заменим несколько пробелов одним пробелом.
Опять же, мы используем вложенные операторы REPLACE. Код ниже преобразует
пробелы в квадратные скобки, а затем обратно в один пробел.

 SELECT REPLACE(REPLACE(REPLACE('это пример с несколькими пробелами',' ','[]'),'][',''),'[]',' ') as msg

Вот как это можно записать. Aa-Zz]%’,@string),1),»)
КОНЕЦ
ВОЗВРАТ
КОНЕЦ

Далее мы используем функцию следующим образом.

 SELECT [dbo].[ufnGetLetters]('122S&*%LFJLDkld;lfs34') как сообщение

Связанные команды SQL и руководства

  • ЗАМЕНА

    • SQL-сервера

  • Использование функции SQL ISNULL()

  • Обзор параметров сортировки SQL Server и примеры

  • Выбор между COALESCE и ISNULL в SQL Server

  • Множество вариантов использования Coalesce в SQL Server

  • Как использовать SQL Server Coalesce для работы со значениями NULL

  • Функция CONCAT и CONCAT_WS в SQL Server

  • Несколько способов объединения значений вместе в SQL Server

  • Начало работы с SQL INNER JOIN

  • SQL INNER JOIN, LEFT JOIN и RIGHT JOINПримеры

  • Учебное пособие по строковым функциям SQL

    • СТР

      • SQL

    • ПАТИНДЕКС SQL
    • SQL ПОДСТРОКА

    • Функция SQL COALESCE

    • SQL CONCAT

сервер sql — альтернативное решение для REPLACE() в столбце 9 ntext0001

Обзор

У меня есть база данных изображений, в которой хранится информация о снимках, сделанных камерами. База данных содержит таблицу изображений , которая имеет 256 столбцов, содержащих информацию о снятом изображении.

Один столбец Комментарии имеют формат ntext и содержат CR/LF.
Еще 21 столбец настроен как ntext .

Я извлекаю данные из базы данных в плоский файл, используя Задачи | Экспорт данных... Функция найдена в SSMS. Затем экспортированные данные передаются внешним партнером в новую систему, которая будет использоваться в ближайшем будущем. Файл экспорта (CSV) содержит около 256 столбцов, из которых 21 столбец может содержать CR/LF.

Проблема

Поле/столбец Комментарии содержит многочисленные CR/LF (SQL: CHAR(13) , CHAR(10) ), что влияет на анализ данных.

Я пытался использовать ЗАМЕНИТЬ(выражение, значение, значение) для поиска CR/LF и замены его на @@ и думал реализовать это во время экспорта с помощью Экспорт данных в SSMS.

Однако функция REPLACE() возвращает

Сообщение 8116, уровень 16, состояние 1, строка 4
Тип данных аргумента ntext недопустим для аргумента 1 функции замены.

… когда я выполняю что-то вроде:

 SELECT 'Start *******************', REPLACE(Comment,'
','@@'), ID, '*********************End' FROM dbo.pictures
ГДЕ комментарий НРАВИТСЯ '%
%';

Демонстрация данных из комментария к столбцу

Выдача следующего оператора:

 SELECT Comment FROM dbo.Pictures
ГДЕ Комментировать как '%
%';

…получит следующий образец Комментарий запись:

 Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.
Seite 148 der Zeitung "The Graphic" vom 4. Август 1906 г. = Новости из-за границы.
«Последняя сцена из всех»: на прошлой неделе открылся ретрит для пожилых актеров недалеко от Мо во Франции.
1. Общий вид дома
2. М. Коклен читает в театре под открытым небом
Замок Арененберг, подаренный императрицей Евгенией кантону Тургови. 
3. Вид из замка [Арененберг] на Боденское озеро.
4. Замок Арененберг
Императрица Евгения подарила швейцарскому кантону Тургови исторический замок Арененберг, где жил Наполеон III. прошли несколько лет его юности. Королева Гортензия после падения первой Империи бежала в Швейцарию и в 1817 году приобрела замок, восхитительно расположенный на берегу Боденского озера. Подарок включает в себя бесценную коллекцию картин, рукописей, книг, старой мебели и гобеленов, среди наиболее важных сувениров - раскладушка Наполеона III и карета, в которой он покинул Седан после своего поражения. Когда изменения будут завершены, замок будет открыт для публики.
5. Первый полет нового летательного аппарата Сантос-Дюмон.
6. Зал в Выборге, где заседала распущенная Дума.
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

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

Регулярные выражения

Я экспортировал данные, а затем выполнил различные регулярные выражения, чтобы исключить CR/LF из данных. Поскольку столбец Комментарий находится в середине данных, мне пришлось попробовать различные строки регулярных выражений:

Строки поиска 

 ([a-zA-Z0-9/,.@():;\s]+) (\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n)([a-zA-Z0-9/,.@( ):;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n\r\n)([a-zA-Z0-9/ ,.@():;"\s]+)
([\w/,.@():;\s']+)(\r\n)([\w/,.@():;"\s]+)
(;")(\r\n)(";)
(;")(\r\n)([\w/,.@():;\s']+)
(\w")(\r\n)([\w/,.@():;\s']+)

Заменить 

 \1@@\3

Это решение не было ни эффективным, ни действенным, так как на приведение в порядок CSV-файла уходило несколько прогонов и часов.

Вопрос

Как заменить CR/LF в столбец ntext с @@ во время экспорта в плоский файл CSV? Есть ли вариант, отличный от REPLACE , который я мог бы использовать?

Ограничения/замечания

  • Службы Integration Services не установлены, поэтому это не вариант.