Шаблон Regex внутри функции SQL Replace?
SELECT REPLACE('<strong>100</strong><b>.00 GB', '%^(^-?\d*\.{0,1}\d+$)%', '');
Я хочу заменить любую разметку между двумя частями числа с помощью указанного выше регулярного выражения, но он не работает. Я не уверен, что это синтаксис regex, что неправильно, потому что я попробовал более простой, например '%[^0-9]%', просто проверить, но он тоже не работает. Кто-нибудь знает, как я могу это достичь?
Ответы
Ответ 1
Вы можете использовать PATINDEX
для поиска первого индекса возникновения шаблона (строки). Затем используйте STUFF, чтобы наполнить другую строку в соответствие шаблону (строке).
Прокрутите каждую строку. Замените все незаконные символы тем, что вы хотите. В вашем случае замените не числовое значение пустым. Внутренний цикл - это если у вас более одного незаконного символа в текущей ячейке цикла.
DECLARE @counter int
SET @counter = 0
WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table))
BEGIN
WHILE 1 = 1
BEGIN
DECLARE @RetVal varchar(50)
SET @RetVal = (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '')
FROM Table
WHERE ID_COLUMN = @counter)
IF(@RetVal IS NOT NULL)
UPDATE Table SET
Column = @RetVal
WHERE ID_COLUMN = @counter
ELSE
break
END
SET @counter = @counter + 1
END
Внимание! Это медленное! Наличие столбца varchar может повлиять. Поэтому использование LTRIM RTRIM может немного помочь. Несмотря на это, он медленный.
Кредит переходит на этот ответ на StackOverFlow.
ИЗМЕНИТЬ
Кредит также отправляется на @srutzky
Изменить (by @Tmdean)
Вместо того, чтобы делать одну строку за раз, этот ответ можно адаптировать к более основанному на множестве решения. Он по-прежнему выполняет итерацию максимального числа нечисловых символов в одной строке, поэтому он не идеален, но я думаю, что это должно быть приемлемым в большинстве ситуаций.
WHILE 1 = 1 BEGIN
WITH q AS
(SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
FROM Table)
UPDATE Table
SET Column = STUFF(Column, q.n, 1, '')
FROM q
WHERE Table.ID_Column = q.ID_Column AND q.n != 0;
IF @@ROWCOUNT = 0 BREAK;
END;
Вы также можете повысить эффективность довольно много, если вы поддерживаете бит столбца в таблице, который указывает, было ли поле еще очищено. (NULL представляет "Неизвестный" в моем примере и должен быть столбцом по умолчанию.)
DECLARE @done bit = 0;
WHILE @done = 0 BEGIN
WITH q AS
(SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
FROM Table
WHERE COALESCE(Scrubbed_Column, 0) = 0)
UPDATE Table
SET Column = STUFF(Column, q.n, 1, ''),
Scrubbed_Column = 0
FROM q
WHERE Table.ID_Column = q.ID_Column AND q.n != 0;
IF @@ROWCOUNT = 0 SET @done = 1;
-- if Scrubbed_Column is still NULL, then the PATINDEX
-- must have given 0
UPDATE table
SET Scrubbed_Column = CASE
WHEN Scrubbed_Column IS NULL THEN 1
ELSE NULLIF(Scrubbed_Column, 0)
END;
END;
Если вы не хотите изменять свою схему, это легко адаптировать для хранения промежуточных результатов в переменной таблицы, которая применяется к фактической таблице в конце.
Ответ 2
В общем смысле SQL Server не поддерживает регулярные выражения, и вы не можете использовать их в собственном T-SQL-коде.
Вы можете написать функцию CLR для этого. Например, здесь.
Ответ 3
Вместо того, чтобы вырезать найденный символ по единственному положению, использование Replace(Column, BadFoundCharacter, '') может быть значительно быстрее. Кроме того, вместо замены одного плохого символа, найденного в каждом столбце, это заменяет все найденные.
WHILE 1 = 1 BEGIN
UPDATE dbo.YourTable
SET Column = Replace(Column, Substring(Column, PatIndex('%[^0-9.-]%', Column), 1), '')
WHERE Column LIKE '%[^0-9.-]%'
If @@RowCount = 0 BREAK;
END;
Я убежден, что это будет работать лучше, чем принятый ответ, хотя бы потому, что он делает меньше операций. Есть и другие способы, которые могут быть быстрее, но у меня нет времени исследовать их прямо сейчас.
Ответ 4
Вот функция, которую я написал для достижения этой цели на основе предыдущих ответов.
CREATE FUNCTION dbo.RepetitiveReplace
(
@P_String VARCHAR(MAX),
@P_Pattern VARCHAR(MAX),
@P_ReplaceString VARCHAR(MAX),
@P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Index INT;
-- Get starting point of pattern
SET @Index = PATINDEX(@P_Pattern, @P_String);
while @Index > 0
begin
--replace matching charactger at index
SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);
SET @Index = PATINDEX(@P_Pattern, @P_String);
end
RETURN @P_String;
END;
Gist
Изменить:
Первоначально у меня была рекурсивная функция, которая не очень хорошо работает с сервером sql, так как имеет ограничение на 32 уровня вложенности, что может привести к ошибке, подобной приведенной ниже, каждый раз, когда вы пытаетесь сделать замены 32+ с помощью функции. Вместо того, чтобы пытаться изменить уровень сервера, чтобы позволить больше вложенности (что может быть опасно, например, разрешить бесконечные циклы), переключение на цикл while имеет гораздо больше смысла.
Превышен максимальный уровень вложенности хранимой процедуры, функции, триггера или представления (предел 32).
Ответ 5
Я наткнулся на этот пост в поисках чего-то другого, но подумал, что упомяну решение, которое я использую, которое гораздо более эффективно - и действительно должно быть реализацией по умолчанию для любой функции при использовании с запросом на основе множеств - которое должно использовать перекрестное применение. табличная функция. Кажется, тема все еще активна, так что, надеюсь, это кому-нибудь пригодится.
Пример времени выполнения некоторых ответов на данный момент, основанный на выполнении запросов на основе рекурсивного набора или скалярной функции, основанный на тестовом наборе строк длиной 1 м, удаляющем символы из случайного newid, варьируется от 34 с до 2 м05 с для примеров цикла WHILE и от 1 м 3 до { навсегда} для примеров функций.
Использование табличной функции с перекрестным применением позволяет достичь той же цели за 10 секунд. Возможно, вам придется настроить его в соответствии с вашими потребностями, такими как максимальная длина, которую он обрабатывает.
Функция:
CREATE FUNCTION [dbo].[RemoveChars](@InputUnit VARCHAR(40))
RETURNS TABLE
AS
RETURN
(
WITH Numbers_prep(Number) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,Numbers(Number) AS
(
SELECT TOP (ISNULL(LEN(@InputUnit),0))
row_number() OVER (ORDER BY (SELECT NULL))
FROM Numbers_prep a
CROSS JOIN Numbers_prep b
)
SELECT
OutputUnit
FROM
(
SELECT
substring(@InputUnit,Number,1)
FROM Numbers
WHERE substring(@InputUnit,Number,1) like '%[0-9]%'
ORDER BY Number
FOR XML PATH('')
) Sub(OutputUnit)
)
Использование:
UPDATE t
SET column = o.OutputUnit
FROM ##t t
CROSS APPLY [dbo].[RemoveChars](t.column) o
Ответ 6
Обертывание решения внутри функции SQL может быть полезно, если вы хотите использовать его повторно. Я даже делаю это на клеточном уровне, поэтому я ставлю это как другой ответ:
CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(300))
RETURNS VARCHAR(300)
BEGIN
DECLARE @str VARCHAR(300) = @string;
DECLARE @Pattern VARCHAR (20) = '%[^a-zA-Z0-9]%';
DECLARE @Len INT;
SELECT @Len = LEN(@String);
WHILE @Len > 0
BEGIN
SET @Len = @Len - 1;
IF (PATINDEX(@Pattern,@str) > 0)
BEGIN
SELECT @str = STUFF(@str, PATINDEX(@Pattern,@str),1,'');
END
ELSE
BEGIN
BREAK;
END
END
RETURN @str
END
Ответ 7
Если вы делаете это только для параметра, входящего в хранимую процедуру, вы можете использовать следующее:
declare @badIndex int
set @badIndex = PatIndex('%[^0-9]%', @Param)
while @badIndex > 0
set @Param = Replace(@Param, Substring(@Param, @badIndex, 1), '')
set @badIndex = PatIndex('%[^0-9]%', @Param)
Ответ 8
Я создал эту функцию, чтобы очистить строку, содержащую не числовые символы в поле времени. Время содержало вопросительные знаки, когда они не добавляли минут, что-то вроде этого 20:??. Функция перебирает каждый символ и заменяет? с 0:
CREATE FUNCTION [dbo].[CleanTime]
(
-- Add the parameters for the function here
@intime nvarchar(10)
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar nvarchar(5)
DECLARE @char char(1)
-- Add the T-SQL statements to compute the return value here
DECLARE @i int = 1
WHILE @i <= LEN(@intime)
BEGIN
SELECT @char = CASE WHEN substring(@intime,@i,1) like '%[0-9:]%' THEN substring(@intime,@i,1) ELSE '0' END
SELECT @ResultVar = concat(@ResultVar,@char)
set @i = @i + 1
END;
-- Return the result of the function
RETURN @ResultVar
END
Ответ 9
Я думаю, что более простой и быстрый подход повторяется для каждого символа алфавита:
DECLARE @i int
SET @i = 0
WHILE(@i < 256)
BEGIN
IF char(@i) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')
UPDATE Table SET Column = replace(Column, char(@i), '')
SET @i = @i + 1
END
