Как заменить несколько символов в SQL?

Я бы серьезно подумал о создание CLR UDF вместо этого и с использованием регулярных выражений (и строка, и шаблон могут быть переданы в качестве параметров) для выполнения полного поиска и замены диапазона символов. Он должен легко превзойти этот SQL UDF.

Одним из полезных приемов в SQL является возможность использовать @var = function(...) для присвоения значения. Если у вас есть несколько записей в вашем наборе записей, ваша переменная назначается несколько раз с побочными эффектами:

declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT '>' UNION ALL
SELECT '<' UNION ALL
SELECT '(' UNION ALL
SELECT ')' UNION ALL
SELECT '!' UNION ALL
SELECT '?' UNION ALL
SELECT '@'

declare @testString varchar(100), @newString varchar(100)

set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
set @newString = @testString

SELECT @newString = Replace(@newString, item, '') FROM @badStrings

select @newString -- returns 'Juliet ro0zs my s0xrzone'

Мне очень нравится решение @Juliett! Я бы просто использовал CTE, чтобы получить все недопустимые символы:

DECLARE @badStrings VARCHAR(100)
DECLARE @teststring VARCHAR(100)

SET @badStrings = '><()!?@'
SET @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'

;WITH CTE AS
(
  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 
  FROM CTE 
  WHERE [Counter] < LEN(@badStrings)
)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '') FROM CTE

SELECT @teststring

Джульетта ro0zs моя s0xrzone

Я предлагаю вам создать скалярную пользовательскую функцию. Это пример (извините заранее, потому что имена переменных на испанском языке):

CREATE FUNCTION [dbo].[Udf_ReplaceChars] (
  @cadena VARCHAR(500),  -- String to manipulate
  @caracteresElim VARCHAR(100),  -- String of characters to be replaced
  @caracteresReem VARCHAR(100)   -- String of characters for replacement
) 
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @cadenaFinal VARCHAR(500), @longCad INT, @pos INT, @caracter CHAR(1), @posCarER INT;
  SELECT
    @cadenaFinal = '',
    @longCad = LEN(@cadena),
    @pos = 1;

  IF LEN(@caracteresElim)<>LEN(@caracteresReem)
    BEGIN
      RETURN NULL;
    END

  WHILE @pos <= @longCad
    BEGIN
      SELECT
        @caracter = SUBSTRING(@cadena,@pos,1),
        @pos = @pos + 1,
        @posCarER = CHARINDEX(@caracter,@caracteresElim);

      IF @posCarER <= 0
        BEGIN
          SET @cadenaFinal = @cadenaFinal + @caracter;
        END
      ELSE
        BEGIN
          SET @cadenaFinal = @cadenaFinal + SUBSTRING(@caracteresReem,@posCarER,1)
        END
    END

  RETURN @cadenaFinal;
END

Вот пример использования этой функции:

SELECT dbo.Udf_ReplaceChars('This is a test.','sat','Z47');

И результат: 7hiZ iZ 4 7eZ7.

Как видите, каждый символ параметра @caracteresElim заменяется символом в той же позиции из параметра @caracteresReem.

Хотя этот вопрос был задан о SQL Server 2005, стоит отметить, что с Sql Server 2017 запрос можно выполнить с помощью новой функции TRANSLATE.

https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

Я надеюсь, что эта информация поможет людям, которые попадут на эту страницу в будущем.

У меня была разовая проблема переноса данных, когда исходные данные не могли правильно выводить некоторые необычные/технические символы, а также вездесущие лишние запятые в CSV.

Мы решили, что для каждого такого символа исходный экстракт должен заменить их чем-то, что было бы распознаваемо как исходной системой, так и SQL Server, который их загружал, но в противном случае не было бы в данных.

Однако это означало, что в разных столбцах разных таблиц эти символы замены будут появляться, и мне придется их заменить. Вложение нескольких функций REPLACE сделало код импорта пугающим и подверженным ошибкам при неправильной оценке размещения и количества скобок, поэтому я написал следующую функцию. Я знаю, что он может обрабатывать столбец в таблице из 3000 строк менее чем за секунду, хотя я не уверен, насколько быстро он масштабируется до таблиц с несколькими миллионами строк.

create function [dbo].[udf_ReplaceMultipleChars]
(
    @OriginalString nvarchar(4000)
  , @ReplaceTheseChars nvarchar(100)
  , @LengthOfReplacement int = 1
)
returns nvarchar(4000)
begin

    declare @RevisedString nvarchar(4000) = N'';
    declare @lengthofinput int =
            (
            select len(@OriginalString)
            );

with AllNumbers
as (select 1 as  Number
    union all
    select Number + 1
    from AllNumbers
    where Number < @lengthofinput)
select @RevisedString += case
                             when (charindex(substring(@OriginalString, Number, 1), @ReplaceTheseChars, 1) - 1) % 2
    = 0 then
                                 substring(
                                              @ReplaceTheseChars
                                            , charindex(
                                                           substring(@OriginalString, Number, 1)
                                                         , @ReplaceTheseChars
                                                         , 1
                                                       ) + 1
                                            , @LengthOfReplacement
                                          )
                             else
                                 substring(@OriginalString, Number, 1)
                         end
    from AllNumbers
    option (maxrecursion 4000);
    return (@RevisedString);
end;

Он работает, отправляя как оцениваемую строку, так и символы для замены (@OriginalString) вместе со строкой парных символов, где первый символ должен быть заменен вторым, третий - четвертым, пятый - шестым и т. д. на (@ReplaceTheseChars).

Вот строка символов, которые мне нужно было заменить, и их замены... [']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓

т. е. открывающая квадратная скобка обозначает апостроф, закрывающая — двойную кавычку. Вы можете видеть, что там были вульгарные дроби, а также символы градусов и диаметров.

Существует @LengthOfReplacement по умолчанию, который включен в качестве отправной точки, если кому-то нужно заменить более длинные строки. Я играл с этим в своем проекте, но основной функцией была замена одного символа.

Условие изложения случая имеет важное значение. Это гарантирует, что он заменяет символ только в том случае, если он найден в вашей переменной @ReplaceTheseChars, и что символ должен быть найден в позиции с нечетным номером (минус 1 из результата charindex гарантирует, что все, что НЕ найдено, возвращает отрицательное значение по модулю). то есть, если вы найдете тильду (~) в позиции 5, она заменит ее запятой, но если при последующем запуске она найдет запятую в позиции 6, она не заменит ее фигурной скобкой ({).

Лучше всего это можно продемонстрировать на примере...

declare @ProductDescription nvarchar(20) = N'abc~def[¦][123';
select @ProductDescription
= dbo.udf_ReplaceMultipleChars(
                                  @ProductDescription
/* NB the doubling up of the apostrophe is necessary in the string but resolves to a single apostrophe when passed to the function */
                                ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓' 
                                , default
                              );
select @ProductDescription
 , dbo.udf_ReplaceMultipleChars(
                                   @ProductDescription
                                 ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓'
/* if you didn't know how to type those peculiar chars in then you can build a string like  this... '[' + nchar(0x0027) + ']"~,{' + nchar(0x00D8) + '}' + nchar(0x00B0) etc */
                                ,
                                 default
                               );

Это вернет значение как после первого прохода через функцию, так и во второй раз следующим образом... abc,def'¼"'123 abc,def'¼"'123

Обновление таблицы будет просто

update a
set a.Col1 = udf.ReplaceMultipleChars(a.Col1,'~,]"',1)
from TestTable a

Наконец (я слышал, вы говорите!), хотя у меня не было доступа к функции перевода, я считаю, что эта функция может довольно легко обработать пример, показанный в документации. Демонстрация функции ПЕРЕВОД

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

который возвращает 2*(3+4)/(7-2), хотя я понимаю, что это может не работать на 2*[3+4]/[7-2] !!

Моя функция подошла бы к этому следующим образом, перечислив каждый заменяемый символ, за которым следует его замена [ --> (, { --> ( и т.

select dbo.udf_ReplaceMultipleChars('2*[3+4]/{7-2}', '[({(])})', 1);

который также будет работать для

select dbo.udf_ReplaceMultipleChars('2*[3+4]/[7-2]', '[({(])})', 1);

Я надеюсь, что кто-то найдет это полезным, и если вы сможете проверить его производительность на больших таблицах, сообщите нам об этом так или иначе!

Одним из вариантов является использование таблицы чисел/подсчета для управления итеративным процессом с помощью запроса на основе псевдомножества.

Общую идею замены символов можно продемонстрировать с помощью простого подхода к таблице символов:

create table charMap (srcChar char(1), replaceChar char(1))
insert charMap values ('a', 'z')
insert charMap values ('b', 'y')


create table testChar(srcChar char(1))
insert testChar values ('1')
insert testChar values ('a')
insert testChar values ('2')
insert testChar values ('b')

select 
coalesce(charMap.replaceChar, testChar.srcChar) as charData
from testChar left join charMap on testChar.srcChar = charMap.srcChar

Затем вы можете использовать подход таблицы подсчета, чтобы выполнить поиск по каждой позиции символа в строке.

create table tally (i int)
declare @i int
set @i = 1
while @i <= 256 begin
    insert tally values (@i)
    set @i = @i + 1
end

create table testData (testString char(10))
insert testData values ('123a456')
insert testData values ('123ab456')
insert testData values ('123b456')

select
    i,
    SUBSTRING(testString, i, 1) as srcChar,
    coalesce(charMap.replaceChar, SUBSTRING(testString, i, 1)) as charData
from testData cross join tally
    left join charMap on SUBSTRING(testString, i, 1) = charMap.srcChar
where i <= LEN(testString)

Я не знаю, почему Чарльз Бретана удалил свой ответ, поэтому я добавляю его обратно в качестве ответа CW, но сохраняемый вычисляемый столбец - это ДЕЙСТВИТЕЛЬНО хороший способ справиться с этими случаями, когда вам почти все время нужны очищенные или преобразованные данные. , но нужно сохранить оригинальный мусор. Его предложение актуально и уместно НЕЗАВИСИМО от того, как вы решите очистить свои данные.

В частности, в моем текущем проекте у меня есть постоянный вычисляемый столбец, который отсекает все начальные нули (к счастью, это действительно легко обрабатывается в прямом T-SQL) из некоторых конкретных числовых идентификаторов, хранящихся непоследовательно с начальными нулями. Он хранится в постоянных вычисляемых столбцах в таблицах, которым он нужен, и индексируется, поскольку этот согласованный идентификатор часто используется в соединениях.

Вот шаги

1. Создайте функцию CLR

См. следующий код:

public partial class UserDefinedFunctions 
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Replace2(SqlString inputtext, SqlString filter,SqlString      replacewith)
{

    string str = inputtext.ToString();
    try
    {
        string pattern = (string)filter;
        string replacement = (string)replacewith;
        Regex rgx = new Regex(pattern);
        string result = rgx.Replace(str, replacement);
        return (SqlString)result;

    }
    catch (Exception s)
    {
        return (SqlString)s.Message;
    }
}
}

1. Разверните свою функцию CLR

2. Теперь проверьте это

См. следующий код:

create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values('P@ssw1rd'),('This 12is @test')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,'[0-9@]','')

select * from dbo.test
dummydata, Psswrd, This is test booom!!!!!!!!!!!!!

Вот современное решение с использованием STRING_SPLIT очень кратко. Недостатком является то, что вам нужна по крайней мере версия SQL Server 2016, работающая на уровне совместимости 130.

Declare @strOriginal varchar(100) = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
Declare @strModified varchar(100) = @strOriginal
Declare @disallowed  varchar(100) = '> < ( ) ! ? @'

Select 
   @strModified = Replace(@strModified, value, '') 
From 
   String_Split(@disallowed,' ')

Select @strModified

Он возвращает:

Juliet ro0zs my s0xrzone