Замена множественному REPLACE или изящные решения

Иногда встаёт задача почистить строки от лишних символов. Если это 1-2 символа, то мы легко и не принуждённо воспользуемся функцией REPLACE, но при необходимости заменить большее количество символов, такое решение не будет изящным.

Вот примерно так выглядит множественный REPLACE. Согласитесь, данное решение совсем не читаемо.

SELECT  F_C_DATA_GR.NDF_CODE, 
	(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
	replace(replace(replace(replace(replace(replace(f_c_data_gr.NAME + ISNULL(f_c_data_gr.FORM, '') + ISNULL(f_c_data_gr.MANUFACTURER, '') + CAST(F_C_DATA_GR.PRICE AS varchar(15))
, '.', ''), ',', ''), '_', ''), '  ', ''), ' ', ''), '|', ''), '№', 'N'), '-', ''), 'і', 'и'), '/', ''), '##', ''), '#', ''), '(', ''), ')', ''), '"', ''), '@', '')
, '$', ''), '**', ''), '%', ''), '+', ''), '*', ''), '=', ''), '~', '')) n
 FROM          F_C_DATA_GR

Тут нам на помощь могут придти следующие методы:

Метод 1 (функция):

Создаём функцию, куда будем передавать строку и необходимые символы для замены:

create function [dbo].[Split](@String varchar(8000), @Delimiter char(1)) 
returns @temptable TABLE (items varchar(8000)) 
as 
begin 
declare @idx int 
declare @slice varchar(8000)

select @idx = 1 
if len(@String)<1 or @String is null return

while @idx!= 0 
begin 
set @idx = charindex(@Delimiter,@String) 
if @idx!=0 
set @slice = left(@String,@idx - 1) 
else 
set @slice = @String

if(len(@slice)>0) 
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx) 
if len(@String) = 0 break 
end 
return 
end

Вызываем функцию:

DECLARE @NEWSTRING VARCHAR(100) 
SET @NEWSTRING = '(N_100-(6858)*(6858)*N_100/0_2)%N_35' ;
SELECT @NEWSTRING = REPLACE(@NEWSTRING, items, '~') FROM dbo.Split('+,-,*,/,%,(,)', ',');
PRINT @NEWSTRING

На выходе будет следующее:

~N_100~~6858~~~6858~~N_100~0_2~~N_35

Вывод: Данный метод не самый лучший, так как имеет проблемы с производительностью и со встраиваемостью в запрос.

Метод 2 (функция, встраиваемая в запрос):

Создаём функцию:

CREATE FUNCTION StringReduce
      (
      @inputstr VARCHAR(4096)
      , @stripchrs VARCHAR(255)
      )
RETURNS VARCHAR(4096)
AS  
BEGIN 
      DECLARE @charcounter INT
      SET @charcounter = 1
      WHILE  @charcounter <= LEN(@stripchrs)
      BEGIN
            SET @inputstr = REPLACE(@inputstr, SUBSTRING(@stripchrs, @charcounter, 1), '')
            SET @charcounter = @charcounter + 1
      END
RETURN @inputstr
END

Создаём временную таблицу, напомняем её данными и встраиваем в запрос:

CREATE TABLE #ManyCharacters
(
name nvarchar(255)
)
INSERT INTO #ManyCharacters VALUES ('ABC!@#%DEFgh')
INSERT INTO #ManyCharacters VALUES ('~!102WXY&*()_Z')

SELECT dbo.StringReduce(name, '@#$^&*()-+_!%~') FROM #ManyCharacters 

DROP TABLE #ManyCharacters

Вывод: Данный метод уже можно использовать при выборке из таблицы, но в теории, скорость работы данного метода ещё не оптимальный

Метод 3 (CTE — обобщённое табличное выражение):

Создаём временную таблицу и заполняем её данными. Именно данная таблица будет использоваться в CTE:

CREATE TABLE #Foobar (
  key_col INT PRIMARY KEY,
  text_col NVARCHAR(100));

INSERT INTO #Foobar VALUES (1, N'ABC!@#%DEFgh');
INSERT INTO #Foobar VALUES (2, N'~!102WXY&*()_Z');

Создаём CTE (в нашем случае мы заменяем символы на пустое место, можно заменять на любое значение, для этого необходимо немного модифицировать CTE):

WITH Clean (key_col, text_col, ch) 
AS
(SELECT key_col,
        REPLACE(text_col, CHAR(255), ''),
        255
 FROM #Foobar
 UNION ALL
 SELECT key_col,
        CASE WHEN
             CHAR(ch - 1) NOT LIKE '[A-Z!]'
             THEN REPLACE(text_col, CHAR(ch - 1), '')
             ELSE text_col END,
        ch - 1
 FROM Clean
 WHERE ch > 1)

Делаем выбоорку и удаляем временную таблицу:

SELECT key_col, text_col 
FROM Clean
WHERE ch = 1
OPTION (MAXRECURSION 255);

DROP TABLE #Foobar

Вывод: Данный метод, на мой взгляд, менее удобный, но подразумевается, что работает быстрее.

Метод 4 (dll):

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

Вывод: Данный метод будет максимально быстрым.

Более подробно про CTE (обобщённое табличное выражение)