Найдите каждый символ в строке и замените символом из другой строки
У меня есть таблица с двумя столбцами строкового типа с именами IL и CL. Мне нужно сравнить обе строки посимвольно, и везде, где есть вопросительный знак в строке столбца CL, мне нужно заменить его символом, найденным в другом столбце (IL), в той же позиции.
Например, строка CL после сравнения с IL и замены вопросительных знаков будет новым CL, как показано ниже.
У меня работает приведенный ниже код, в котором внутри while есть цикл cursor, проходящий через каждый символ строки, объединяющийся в новую строку, а затем выполняющий обновление в конце с новым значением. Однако производительность этого фрагмента кода ужасно низкая, поскольку в таблице более 100 тыс. записей, и каждая строка обрабатывается 25 раз (длина строки). Я ищу, есть ли способ переписать этот набор логики для повышения производительности.
declare CharC insensitive cursor for
select ID, IL, CL
from AcctStrings
where CL like '%?%'
open CharC
fetch next from CharC into @ID, @IL, @CL
while @@fetch_status = 0 begin
set @NewCL = ''
set @i = 1
while @i <= 25 begin
set @TestChar = substring(@CL,@i,1)
set @OtherChar = substring(@IL,@i,1)
if (@TestChar = '?') begin
set @NewCL = @NewCL + @OtherChar
end
else
set @NewCL = @NewCL + @TestChar
set @i = @i + 1
end
update AcctStrings
set CL = @NewCL
where ID = @ID
end
fetch next from CharC into @ID, @IL, @CL
end
deallocate CharC
Ответы 1
Если вы используете SQL Server 2022, вы можете попробовать следующее:
UPDATE AcctStrings
SET CL = (
SELECT STRING_AGG(N.NewC, '') WITHIN GROUP(ORDER BY S.Value)
FROM GENERATE_SERIES(1, LEN(CL)) S
CROSS APPLY(SELECT SUBSTRING(CL, S.value, 1) AS C) O
CROSS APPLY(SELECT CASE WHEN O.C = '?' THEN SUBSTRING(IL, S.value, 1) ELSE O.C END AS NewC) N
)
WHERE CL LIKE '%?%'
В приведенном выше примере используется подзапрос, чтобы разбить строку CL на отдельные символы, применить преобразование (при необходимости), а затем восстановить обновленную строку.
См. эту db<>fiddle для демонстрации.
Результаты (с некоторыми дополнительными данными испытаний):
???-123201-000000-000-???104-234561-644221-123-947104-123201-000000-000-947111-222222-333333-444-555xxx-xxxxxx-xxxxxx-xxx-xxx
нулевой
?????????????????????????123-654321-123456-456-789123-654321-123456-456-789Нулевое значение указывает, что значение NewCL не было рассчитано, поскольку исходное значение не содержало знаков "?".
Приложение:
Для SQL Server 2019 функция GENERATE_SERIES() недоступна, но существует несколько альтернативных методов генерации числовой последовательности — подвыбор VALUES, ROW_NUMBER() применяемый к достаточно большому источнику строк, или рекурсивное CTE (общее табличное выражение).
В следующем примере используется рекурсивный CTE для генерации числовой последовательности, которая затем ограничивается длиной строки CL в предложении WHERE. Остальная логика та же.
;WITH Series AS (
SELECT 1 AS Value
UNION ALL
SELECT S.Value + 1 AS Value
FROM Series S
WHERE S.Value < 99 -- At least the maximum expected length of CL
)
UPDATE AcctStrings
SET CL = (
SELECT STRING_AGG(N.NewC, '') WITHIN GROUP(ORDER BY S.Value)
FROM Series S
CROSS APPLY(SELECT SUBSTRING(CL, S.value, 1) AS C) O
CROSS APPLY(SELECT CASE WHEN O.C = '?' THEN SUBSTRING(IL, S.value, 1) ELSE O.C END AS NewC) N
WHERE S.Value <= LEN(CL)
)
WHERE CL LIKE '%?%'
См. эту db<>скрипку.
В 2016 году нам нужно вернуться к старой FOR XML PATH('') методике.
...
SET NewCL = (
SELECT (
SELECT N.NewC as [text()]
FROM Series S
CROSS APPLY(SELECT SUBSTRING(CL, S.value, 1) AS C) O
CROSS APPLY(SELECT CASE WHEN O.C = '?' THEN SUBSTRING(IL, S.value, 1) ELSE O.C END AS NewC) N
WHERE S.Value <= LEN(CL)
ORDER BY S.value
FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)')
)
...
Поскольку мы не вставляем разделители, STUFF() в этом варианте использования можно опустить. Обычно объединяемое выражение по умолчанию представляет собой элемент text() (обычный текст), но поскольку это прямая ссылка на столбец, необходимо явное AS [text()], чтобы избежать форматирования значения как <NewC>x</NewC>. Использование , TYPE и .value('text()[1]', 'nvarchar(max)') рекомендуется, чтобы избежать проблем с кодировкой символов.
Посмотрите эту скрипку 2016 года.
К сожалению, я использую SQL Server 2019. Есть ли другой способ для версий 2019 или более ранних версий?
Я обновил свой ответ выше для SQL Server 2019.
Спасибо вам за помощь. Я не знал об этих функциях STRING_AGG() или GENERATE_SERIES(). Я тестировал ваш пример и обнаружил, что с помощью этого скрипта мне также необходимо поддерживать SQL 2016 и 2019. Я думаю, мне придется использовать STUFF и XML PATH, поскольку другие функции недоступны до SQL 2017.
Я снова обновил приведенный выше ответ на 2016 год.
Какую версию SQL_SERVER вы используете? Запустите команду
select @@version. Один из возможных ответов может включать использованиеGENERATE_SERIES(),SUBSTRING(),CASEиSTRING_AGG(). Некоторые из этих функций являются недавними дополнениями к SQL Server.