Иногда встаёт задача почистить строки от лишних символов. Если это 1-2 символа, то мы легко и не принуждённо воспользуемся функцией REPLACE, но при необходимости заменить большее количество символов, такое решение не будет изящным.
Вот примерно так выглядит множественный REPLACE. Согласитесь, данное решение совсем не читаемо.
|
1 2 3 4 5 6 |
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 (функция):
Создаём функцию, куда будем передавать строку и необходимые символы для замены:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 |
Вызываем функцию:
|
1 2 3 4 |
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 |
На выходе будет следующее:
|
1 |
~N_100~~6858~~~6858~~N_100~0_2~~N_35 |
Вывод: Данный метод не самый лучший, так как имеет проблемы с производительностью и со встраиваемостью в запрос.
Метод 2 (функция, встраиваемая в запрос):
Создаём функцию:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
Создаём временную таблицу, напомняем её данными и встраиваем в запрос:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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) |
Делаем выбоорку и удаляем временную таблицу:
|
1 2 3 4 5 6 |
SELECT key_col, text_col FROM Clean WHERE ch = 1 OPTION (MAXRECURSION 255); DROP TABLE #Foobar |
Вывод: Данный метод, на мой взгляд, менее удобный, но подразумевается, что работает быстрее.
Метод 4 (dll):
Так же можно написать dll, подключить её и с помощью программирования обработать все необходимые символы.
Вывод: Данный метод будет максимально быстрым.
Более подробно про CTE (обобщённое табличное выражение)
