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

Ответ 1

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

Ответ 2

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

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()[email protected][email protected][email protected]!'
set @newString = @testString

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

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

Ответ 3

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

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

SET @badStrings = '><()[email protected]'
SET @teststring = 'Juliet ro><0zs my s0x()[email protected][email protected][email protected]!'

;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

Ответ 4

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

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.

Ответ 5

declare @testVal varchar(20)

set @testVal = '?t/es?ti/n*g 1*2?3*'

select @testVal = REPLACE(@testVal, item, '') from (select '?' item union select '*' union select '/') list

select @testVal;

Ответ 6

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

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

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)

Ответ 7

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

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

Ответ 8

Ниже приведены шаги

• Создать функцию 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;
    }
}
}

• Разверните функцию CLR

• Теперь протестируйте его

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

create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values('[email protected]'),('This 12is @test')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,'[[email protected]]','')

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

Ответ 9

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

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

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

Ответ 10

У меня была проблема с одноразовой миграцией данных, когда исходные данные не могли правильно выводить некоторые необычные/технические символы плюс вездесущие дополнительные запятые в 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);

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