在 SQL 查询中执行正则表达式（替换）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/150977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Perform regex (replace) in an SQL query
提问by alumb
What is the best way to replace all '<' with <in a given database column? Basically perform s/<[^;]/</gi
<在给定的数据库列中替换所有 '<' 的最佳方法是什么？基本上执行s/<[^;]/</gi
Notes:
笔记：
- must work in MS SQL Server2000
- Must be repeatable (and not end up with
<;;;;;;;;;)
- 必须在MS SQL Server2000 中工作
- 必须是可重复的（而不是结束
<;;;;;;;;;）
采纳答案by Jorge Ferreira
Some hacking required but we can do this with LIKE, PATINDEX, LEFTAND RIGHTand good old string concatenation.
需要一些黑客攻击，但我们可以使用LIKE、PATINDEX、LEFT和RIGHT以及好的旧字符串连接来做到这一点。
create table test
(
id int identity(1, 1) not null,
val varchar(25) not null
)
insert into test values ('< <- ok, < <- nok')
while 1 = 1
begin
update test
set val = left(val, patindex('%<[^;]%', val) - 1) +
'<' +
right(val, len(val) - patindex('%<[^;]%', val) - 2)
from test
where val like '%<[^;]%'
IF @@ROWCOUNT = 0 BREAK
end
select * from test
Better is that this is SQL Server version agnostic and should work just fine.
更好的是，这是 SQL Server 版本不可知的，应该可以正常工作。
回答by leoinfo
I think this can be done much cleaner if you use different STUFF :)
我认为如果你使用不同的东西，这可以做得更干净:)
create table test
(
id int identity(1, 1) not null,
val varchar(25) not null
)
insert into test values ('< <- ok, < <- nok')
WHILE 1 = 1
BEGIN
UPDATE test SET
val = STUFF( val , PATINDEX('%<[^;]%', val) + 3 , 0 , ';' )
FROM test
WHERE val LIKE '%<[^;]%'
IF @@ROWCOUNT = 0 BREAK
END
select * from test
回答by ilitirit
How about:
怎么样：
UPDATE tableName
SET columName = REPLACE(columName , '<', '<')
WHERE columnName LIKE '%lt%'
AND columnName NOT LIKE '%lt;%'
Edit:
编辑：
I just realized this will ignore columns with partially correct <strings.
我刚刚意识到这将忽略具有部分正确<字符串的列。
In that case you can ignore the second part of the where clause and call this afterward:
在这种情况下，您可以忽略 where 子句的第二部分，然后调用它：
UPDATE tableName
SET columName = REPLACE(columName , '<;', '<')
回答by Dillie-O
This articlecovers how to create a simple Regex Replace function that you can use in SQL 2000 (and 2005 with simple tweak) that can assist you.
本文介绍了如何创建一个简单的 Regex Replace 函数，您可以在 SQL 2000（和 2005 中通过简单的调整）中使用它来帮助您。
回答by Dave Sherohman
If MSSQL's regex flavor supports negative lookahead, that would be The Right Way to approach this.
如果 MSSQL 的正则表达式风格支持负前瞻，那将是解决这个问题的正确方法。
s/<(?!;)/</gi
will catch all instances of <which are not followed by a ;(even if they're followed by nothing, which [^;]would miss) and does not capture the following non-;character as part of the match, eliminating the issue mentioned in the comments on the original question of that character being lost in the replacement.
将捕获< 的所有实例，其后不跟一个; （即使它们后面没有任何内容，[^;]会错过）并且不会捕获以下非; 字符作为匹配的一部分，消除了关于该字符在替换中丢失的原始问题的评论中提到的问题。
Unfortunately, I don't use MSSQL, so I have no idea whether it supports negative lookahead or not...
不幸的是，我不使用 MSSQL，所以我不知道它是否支持负前瞻...
回答by Kristen
Very specific to this pattern, but I have done similar to this in the past:
非常特定于这种模式，但我过去做过类似的事情：
REPLACE(REPLACE(columName, '<', '<'), '<', '<')
REPLACE(REPLACE(columName, '<', '<'), '<', '<')
broader example (encode characters which may be inappropriate in a TITLE attribute)
更广泛的示例（对 TITLE 属性中可能不合适的字符进行编码）
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
columName
-- Remove existing encoding:
, '&', '&')
, '"', '"')
, ''', '''')
-- Reinstate/Encode:
, '&', '&')
-- Encode:
, '"', '"')
, '''', ''')
, ' ', '%20')
, '<', '%3C')
, '>', '%3E')
, '/', '%2F')
, '\', '%5C')
