Блог

У меня есть таблица со списком недопустимых символов, таких как:

 InVCh
-----

!
"
$
%
amp;
'
(
)
*
 
,
.
/
  

Затем у меня есть много таблиц с разным количеством столбцов (все эти столбцы имеют строковый тип), пример:

 Product          Store
-------          ------
Prod1            Store1
Pr$od!2          Sto$re!2
P:;()ro!!!"d3    S:;()to!!!"re3
  

Я хотел бы создать процедуру, которая находит все эти недопустимые символы и заменяет их пробелом, если пробелов слишком много, тогда я должен заменить их одним пробелом. Итак, мой ожидаемый результат должен быть:

 Product          Store
-------          ------
Prod1            Store1
Pr od 2          Sto re 2
P ro d3          S to re3
  

Это возможно?

Спасибо!

Поскольку это SQL Server 2016, использование R является опцией. Это не кажется таким уж надуманным, поскольку есть статья MSSQLTips от 2017 года, которая описывает это: Регулярные выражения SQL Server 2016 на языке R.

Код статьи тоже не такой сложный :

 create table dbo.tblRegEx (id int identity, a varchar(300), b  varchar(300) );

-- 3. Remove duplicate words
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern <-"\b(\w \s*)(\1\s*) ";
inData$a <- gsub(pattern, "\1", inData$a, perl = T );
outData <- inData;'
, @input_data_1 = N'select id, a, b from dbo.tblRegEx'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( as object dbo.tblRegEx);
  

Этот вопрос требует чего-то гораздо более простого, просто замените некоторые символы.

 create table #products 
(
    id int primary key identity, 
    product varchar(300), 
    store  varchar(300) 
);
go

insert into #products (product,store)
values 
('Prod1',            'Store1'),
('Pr$od!2',          'Sto$re!2'),
('P:;()ro!!!"d3',    'S:;()to!!!"re3')

exec sp_execute_external_script @language=N'R'
, @script = N'
pattern <-"[!"$%amp;''()* ,./:;] ";
inData$product <- gsub(pattern, " ", inData$product, perl = T );
inData$store <- gsub(pattern, " ", inData$store, perl = T );
outData <- inData;'
, @input_data_1 = N'select id, product, store from #products'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( as object #products);
  

Как и все хранимые процедуры, результаты могут быть возвращены только клиенту или использованы в качестве источника для INSERT INTO . Это может относиться к заявленной или временной таблице или табличной переменной, которая может использоваться для обновления исходной таблицы :

 declare @outData table (id int primary key, product varchar(300), store  varchar(300) );

insert into @outData
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern <-"[!"$%amp;''()* ,./:;] ";   
inData$product <- gsub(pattern, " ", inData$product, perl = T );
inData$store <- gsub(pattern, " ", inData$store, perl = T );
outData <- inData;'
, @input_data_1 = N'select id, product, store from #products'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData' 



update #products
set product = r.product,
    store   = r.store
from #products inner join @outdata r on r.id=#products.id

select * from #products
  

Это возвращает :

 id  product   store
--  -------   --------
1   Prod1     Store1
2   Pr od 2   Sto re 2
3   P ro d3   S to re3
  

Я предполагаю, что без версии у вас есть доступ к новейшим инструментам. Поэтому вы могли бы использовать FOR XML PATH для создания строки из символов, которые нуждаются в замене, а затем TRANSLATE для избавления от них всех:

 WITH C AS(
    SELECT *
    FROM (VALUES('!'),
                ('"'),
                ('$'),
                ('%'),
                ('amp;'),
                (''''),
                ('('),
                (')'),
                ('*'),
                (' '),
                (','),
                ('.'),
                ('/'))V(InVCh)),
PS AS (
    SELECT *
    FROM (VALUES('Prod1','Store1'),
                ('Pr$od!2','Sto$re!2'),
                ('P:;()ro!!!"d3','S:;()to!!!"re3')) V(Product,Store))
SELECT REPLACE(TRANSLATE(PS.Product,V.C,REPLICATE(LEFT(V.C,1),LEN(V.C))),LEFT(V.C,1),'') AS Product,
        REPLACE(TRANSLATE(PS.Store,V.C,REPLICATE(LEFT(V.C,1),LEN(V.C))),LEFT(V.C,1),'') AS Store
FROM PS
     CROSS APPLY (VALUES((SELECT ''   InVCh
                          FROM C
                          FOR XML PATH(''),TYPE).value('.','varchar(MAX)')))V(C);
  

db<>скрипта

Обратите внимание, что возвращаемым значением для 3-й строки является 'P:;rod3' и 'S:;tore3' , поскольку в вашем списке символов, подлежащих удалению, нет ни точки с запятой ( ; ), ни двоеточия ( : ). Вам нужно будет добавить все символы, которые необходимо заменить.

Кажется, OP упомянул в комментариях, что они используют 2016 (почему важно знать, какую версию вы используете!). Используя Ngrams8K , вы могли бы сделать это (хотя выглядит неаккуратно):

 WITH C AS(
    SELECT *
    FROM (VALUES('!'),
                ('"'),
                ('$'),
                ('%'),
                ('amp;'),
                (''''),
                ('('),
                (')'),
                ('*'),
                (' '),
                (','),
                ('.'),
                ('/'))V(InVCh)),
PS AS (
    SELECT *
    FROM (VALUES(1,'Prod1','Store1'),
                (2,'Pr$od!2','Sto$re!2'),
                (3,'P:;()ro!!!"d3','S:;()to!!!"re3')) V(ID,Product,Store))
SELECT PS.Product,V.Product,
       PS.Store,V.Store
FROM PS
     CROSS APPLY (VALUES((SELECT ''   N.token
                          FROM dbo.NGrams8k(PS.Product,1) N
                          WHERE NOT EXISTS (SELECT 1
                                            FROM C
                                            WHERE C.InVCh = N.token)
                          ORDER BY position
                          FOR XML PATH(''),TYPE).value('.','varchar(8000)'),
                         (SELECT ''   N.token
                          FROM dbo.NGrams8k(PS.Store,1) N
                          WHERE NOT EXISTS (SELECT 1
                                            FROM C
                                            WHERE C.InVCh = N.token)
                          ORDER BY position
                          FOR XML PATH(''),TYPE).value('.','varchar(8000)')))V(Product,Store)