如何替换 SQL 中的多个字符? [英] How to Replace Multiple Characters in SQL?

问题描述

这是基于一个类似的问题 How to Replace Multiple Characters in访问 SQL?

This is based on a similar question How to Replace Multiple Characters in Access SQL?

我写这个是因为 sql server 2005 似乎将 replace() 函数限制为 where 子句中的 19 个替换.

I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where clause.

我有以下任务:需要对列执行匹配，并使用 replace() 函数提高匹配去除多个不需要的字符的机会

I have the following task: Need to perform a match on a column, and to improve the chances of a match stripping multiple un-needed chars using replace() function

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p0 NVarChar(1) SET @p0 = '!'
DECLARE @p1 NVarChar(1) SET @p1 = '@'
---etc...

SELECT *
FROM t1,t2 
WHERE  REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
     = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)    
---etc 

如果在那个 where 子句中有 >19 个 REPLACE()，它就不起作用.所以我想出的解决方案是在这个例子中创建一个名为 trimChars 的 sql 函数(请原谅他们从@22 开始

If there are >19 REPLACE() in that where clause, it doesn't work. So the solution I came up with is to create a sql function called trimChars in this example (excuse them starting at @22

CREATE FUNCTION [trimChars] (
   @string varchar(max)
) 

RETURNS varchar(max) 
AS
BEGIN

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p22 NVarChar(1) SET @p22 = '^'
DECLARE @p23 NVarChar(1) SET @p23 = '&'
DECLARE @p24 NVarChar(1) SET @p24 = '*'
DECLARE @p25 NVarChar(1) SET @p25 = '('
DECLARE @p26 NVarChar(1) SET @p26 = '_'
DECLARE @p27 NVarChar(1) SET @p27 = ')'
DECLARE @p28 NVarChar(1) SET @p28 = '`'
DECLARE @p29 NVarChar(1) SET @p29 = '~'
DECLARE @p30 NVarChar(1) SET @p30 = '{'

DECLARE @p31 NVarChar(1) SET @p31 = '}'
DECLARE @p32 NVarChar(1) SET @p32 = ' '
DECLARE @p33 NVarChar(1) SET @p33 = '['
DECLARE @p34 NVarChar(1) SET @p34 = '?'
DECLARE @p35 NVarChar(1) SET @p35 = ']'
DECLARE @p36 NVarChar(1) SET @p36 = '\'
DECLARE @p37 NVarChar(1) SET @p37 = '|'
DECLARE @p38 NVarChar(1) SET @p38 = '<'
DECLARE @p39 NVarChar(1) SET @p39 = '>'
DECLARE @p40 NVarChar(1) SET @p40 = '@'
DECLARE @p41 NVarChar(1) SET @p41 = '-'

return   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       @string, @p22, @es), @p23, @es), @p24, @es), @p25, @es), @p26, @es), @p27, @es), @p28, @es), @p29, @es), @p30, @es), @p31, @es), @p32, @es), @p33, @es), @p34, @es), @p35, @es), @p36, @es), @p37, @es), @p38, @es), @p39, @es), @p40, @es), @p41, @es)
END 

这可以与其他替换字符串一起使用

This can then be used in addition to the other replace strings

SELECT *
FROM t1,t2 
WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))   

我创建了更多的函数来做类似的替换 trimChars(trimMoreChars(

I created a few more functions to do similar replacing like so trimChars(trimMoreChars(

SELECT *
FROM t1,t2 
WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)))

有人可以在性能方面给我一个更好的解决方案吗?也许更简洁的实现?

Can someone give me a better solution to this problem in terms of performance and maybe a cleaner implementation?

推荐答案

我会认真考虑 制作 CLR UDF 代替 并使用正则表达式(字符串和模式都可以传入作为参数)进行完整搜索并替换一系列字符.它应该很容易胜过这个 SQL UDF.

I would seriously consider making a CLR UDF instead and using regular expressions (both the string and the pattern can be passed in as parameters) to do a complete search and replace for a range of characters. It should easily outperform this SQL UDF.

这篇关于如何替换 SQL 中的多个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！