我正在尝试从表中的多个列中删除非字母数字字符,并且没有权限创建函数或临时函数。我想知道是否有人在不创建任何函数的情况下删除非字母数字字符的经验?谢谢。我使用的是MS SQL Server Management Studio v17.9.1。
我正在尝试从表中的多个列中删除非字母数字字符,并且没有权限创建函数或临时函数。我想知道是否有人在不创建任何函数的情况下删除非字母数字字符的经验?谢谢。我使用的是MS SQL Server Management Studio v17.9.1。
while @@rowcount > 0
update user_list_original
set fname = replace(fname, substring(fname, patindex('%[^a-zA-Z ]%', fname), 1), '')
where patindex('%[^a-zA-Z ]%', fname) <> 0
这里是一个起点 - 您需要调整它以适应所有需要清洗的列:
;WITH allcharcte ( id, textcol1, textcol2, textcol1where, textcol2where )
AS (SELECT id,
CAST(textcol1 AS NVARCHAR(255)),
CAST(textcol2 AS NVARCHAR(255)),
-- Start the process of looking for non-alphanumeric chars in each
-- of the text columns. The returned value from PATINDEX is the position
-- of the non-alphanumeric char and is stored in the *where columns
-- of the CTE.
PATINDEX(N'%[^0-9A-Z]%', textcol1),
PATINDEX(N'%[^0-9A-Z]%', textcol2)
FROM #temp
UNION ALL
-- This is the recursive part. It works through the rows which have been
-- returned thus far processing them for use in the next iteration
SELECT prev.id,
-- If the *where column relevant for each of the columns is NOT NULL
-- and NOT ZERO, then use the STUFF command to replace the char
-- at that location with an empty string
CASE ISNULL(prev.textcol1where, 0)
WHEN 0 THEN CAST(prev.textcol1 AS NVARCHAR(255))
ELSE CAST(STUFF(prev.textcol1, prev.textcol1where, 1, N'') AS NVARCHAR(255))
END,
CASE ISNULL(prev.textcol2where, 0)
WHEN 0 THEN CAST(prev.textcol2 AS NVARCHAR(255))
ELSE CAST(STUFF(prev.textcol2, prev.textcol2where, 1, N'') AS NVARCHAR(255))
END,
-- We now check for the existence of the next non-alphanumeric
-- character AFTER we replace the most recent finding
ISNULL(PATINDEX(N'%[^0-9A-Z]%', STUFF(prev.textcol1, prev.textcol1where, 1, N'')), 0),
ISNULL(PATINDEX(N'%[^0-9A-Z]%', STUFF(prev.textcol2, prev.textcol2where, 1, N'')), 0)
FROM allcharcte prev
WHERE ISNULL(prev.textcol1where, 0) > 0
OR ISNULL(prev.textcol2where, 0) > 0)
SELECT *
FROM allcharcte
WHERE textcol1where = 0
AND textcol2where = 0
本质上,它是一个递归CTE,将通过PATINDEX(N'%[^0-9A-Z]%', <column>)
找到的任何非字母数字字符重复替换为空字符串(通过STUFF(<column>, <where>, N'')
)。通过复制这些块,您应该能够将其适应于任意数量的列。
编辑:如果您预计需要从任何一列中剥离超过100个非字母数字字符的实例,则需要在调用之前调整MAXRECURSION属性。
replace()
。 - Gordon Linoff