SQL如何删除非字母数字字符而不创建函数？

Question

我正在尝试从表中的多个列中删除非字母数字字符，并且没有权限创建函数或临时函数。我想知道是否有人在不创建任何函数的情况下删除非字母数字字符的经验？谢谢。我使用的是MS SQL Server Management Studio v17.9.1。

- cheklapkok

---

你可以频繁地运行 replace()。 - Gordon Linoff

你是指每一列都要替换吗？ - cheklapkok

我认为@GordonLinoff指的是这个问题中OP使用的代码：T-SQL去除所有非字母和非数字字符。 - Florian Lim

@Forty3 我需要从数据库表中的几列中剥离所有非字母数字字符，但我没有创建函数、临时函数或过程的权限。因此，我想知道是否有一种较为困难的方法来完成这个任务。 - cheklapkok

@Forty3 是的，可以在单个选择语句中完成吗？ - cheklapkok

Answer 1

如果您必须使用单个SELECT查询，就像@Forty3提到的那样，那么多个REPLACE（如@Gordon-Linoff所说）可能是最好的选择（但绝对不是理想的选择）。如果您可以更新数据或使用T-SQL，那么您可以像这样做：https://searchsqlserver.techtarget.com/tip/Replacing-non-alphanumeric-characters-in-strings-using-T-SQL。

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

- Duke Silver

---

我目前正在尝试这个解决方案，但似乎需要很长时间。由于我有数百万行数据，我不确定它是否能够完成。 - cheklapkok

没有SQL，只有非功能性的解决方案，在处理数百万行数据时表现良好。 - Duke Silver

Answer 2

这里是一个起点 - 您需要调整它以适应所有需要清洗的列：

;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属性。

- Forty3

---

谢谢。我会尝试一下并告诉你结果。你知道吗，由于我有数百万行数据，这个过程需要很长时间吗？ - cheklapkok

数百万行？可能不会非常快。当然，如果您知道数据集有数百万行，并且不允许访问编写函数/过程，则会引发更多问题，这些问题可能远远超出了SO的范围。祝好运！ - Forty3

是的，如果太耗时间的话我可能会尝试其他方法。有一件事我想问你，因为我对此不是很了解，那就是你能否解释一下textcol和textcolwhere是什么？ - cheklapkok