替换为通配符，在 SQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2740799/
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
Replace with wildcard, in SQL
提问by Jay
I know MS T-SQL does not support regular expression, but I need similar functionality. Here's what I'm trying to do:
我知道 MS T-SQL 不支持正则表达式，但我需要类似的功能。这是我想要做的：
I have a varchar table field which stores a breadcrumb, like this:
我有一个存储面包屑的 varchar 表字段，如下所示：
/ID1:Category1/ID2:Category2/ID3:Category3/
/ID1:Category1/ID2:Category2/ID3:Category3/
Each Category name is preceded by its Category ID, separated by a colon. I'd like to select and display these breadcrumbs but I want to remove the Category IDs and colons, like this:
每个类别名称前面都有其类别 ID，用冒号分隔。我想选择并显示这些面包屑，但我想删除类别 ID 和冒号，如下所示：
/Category1/Category2/Category3/
/类别1/类别2/类别3/
Everything betweenthe leading slash (/) up to and including the colon (:) should be stripped out.
一切之间的斜线（/）直至并包括冒号（:)应该被剥离出来。
I don't have the option of extracting the data, manipulating it externally, and re-inserting back into the table; so I'm trying to accomplish this in a SELECT statement.
我无法选择提取数据，在外部对其进行操作，然后重新插入表中；所以我试图在 SELECT 语句中完成这个。
I also can't resort to using a cursor to loop through each row and clean each field with a nested loop, due to the number of rows returned in the SELECT.
由于 SELECT 中返回的行数，我也无法使用游标来遍历每一行并使用嵌套循环清理每个字段。
Can this be done?
这能做到吗？
Thanks all - Jay
谢谢大家 - 杰
回答by Maashu
I think your best bet is going to be to use a recursive user-defined function (UDF). I've included some code here that you can use to pass in a string to achieve the results you're looking for.
我认为您最好的选择是使用递归用户定义函数 (UDF)。我在此处包含了一些代码，您可以使用这些代码传入字符串以实现您要查找的结果。
CREATE FUNCTION ufn_StripIDsFromBreadcrumb (@cIndex int, @breadcrumb varchar(max), @theString varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @nextColon int
DECLARE @nextSlash int
SET @nextColon = CHARINDEX(':', @theString, @cIndex)
SET @nextSlash = CHARINDEX('/', @theString, @nextColon)
SET @breadcrumb = @breadcrumb + SUBSTRING(@theString, @nextColon + 1, @nextSlash - @nextColon)
IF @nextSlash != LEN(@theString)
BEGIN
exec @breadcrumb = ufn_StripIDsFromBreadcrumb @cIndex = @nextSlash, @breadcrumb = @breadcrumb, @theString = @theString
END
RETURN @breadcrumb
END
You could then execute it with:
然后你可以执行它：
DECLARE @myString varchar(max)
EXEC @myString = ufn_StripIDsFromBreadcrumb 1, '/', '/ID1:Category1/ID2:Category2/ID3:Category3/'
PRINT @myString
回答by Oliver
This works for SQL Server 2005 and up.
这适用于 SQL Server 2005 及更高版本。
create table strings (
string varchar(1000)
)
insert into strings values( '/ID1:Category1/ID2:Category2/ID3:Category3/' )
insert into strings values( '/ID4:Category4/ID5:Category5/ID8:Category6/' )
insert into strings values( '/ID7:Category7/ID8:Category8/ID9:Category9/' )
go
with
replace_with_wildcard ( restrung ) as
(
select replace( string, '', '' )
from strings
union all
select
replace( restrung, substring( restrung, patindex( '%ID%', restrung ), 4 ), '' )
from replace_with_wildcard
where patindex( '%ID%', restrung ) > 0
)
select restrung
from replace_with_wildcard
where charindex( ':', restrung ) = 0
order by restrung
drop table strings
回答by Thomas
You might be able to do this using a Split function. The following split function relies on the existence of a Numbers table which literally contains a sequential list of numbers like so:
您可以使用 Split 函数来执行此操作。以下拆分函数依赖于 Numbers 表的存在，该表实际上包含一个数字的顺序列表，如下所示：
Create Table dbo.Numbers( Value int not null primary key clustered )
GO
With Nums As
(
Select ROW_NUMBER() OVER( Order By o.object_id ) As Num
From sys.objects as o
cross join sys.objects as o2
)
Insert dbo.Numbers( Value )
Select Num
From Nums
Where Num Between 1 And 10000
GO
Create Function [dbo].[udf_Split] (@DelimitedList nvarchar(max), @Delimiter nvarchar(2) = ',')
Returns @SplitResults TABLE (Position int NOT NULL PRIMARY KEY, Value nvarchar(max))
AS
/*
PURPOSE: to split the @DelimitedList based on the @Delimter
DESIGN NOTES:
1. In general the contents of the next item is: NextDelimiterPosition - CurrentStartPosition
2. CurrentStartPosition =
CharIndex(@Delimiter, A.list, N.Value) = Current Delimiter position
+ Len(@Delimiter) + The number of delimiter characters
+ 1 + 1 since the text of the item starts after the delimiter
3. We need to calculate the delimiter length because the LEN function excludes trailing spaces. Thus
if a delimiter of ", " (a comma followed by a space) is used, the LEN function will return 1.
4. The DataLength function returns the number of bytes in the string. However, since we're using
an nvarchar for the delimiter, the number of bytes will double the number of characters.
*/
Begin
Declare @DelimiterLength int
Set @DelimiterLength = DataLength(@Delimiter) / 2
If Left(@DelimitedList, @DelimiterLength) <> @Delimiter
Set @DelimitedList = @Delimiter + @DelimitedList
If Right(@DelimitedList, @DelimiterLength) <> @Delimiter
Set @DelimitedList = @DelimitedList + @Delimiter
Insert @SplitResults(Position, Value)
Select CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength
, Substring (
A.List
, CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength
, CharIndex(@Delimiter, A.list, N.Value + 1)
- ( CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength )
)
From dbo.Numbers As N
Cross Join (Select @DelimitedList As list) As A
Where N.Value > 0
And N.Value < LEN(A.list)
And Substring(A.list, N.Value, @DelimiterLength) = @Delimiter
Order By N.Value
Return
End
You then might be able to run a query like so where you strip out the prefixes:
然后，您可能可以像这样运行查询，在去掉前缀的地方：
Select Table, Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
From Table
Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S
This would give you values like:
这将为您提供以下值：
Category1
Category2
Category3
You could then use FOR XML PATHto combine them again:
然后您可以使用FOR XML PATH再次组合它们：
Select Table.PK
, Stuff( (
Select '/' + Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
From Table As Table1
Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S1
Where Table1.PK = Table.PK
Order By S1.Position
For Xml Path('')
), 1, 1, '') As BreadCrumb
From Table
回答by OMG Ponies
For SQL Server 2005+, you can get regex support by:
对于 SQL Server 2005+，您可以通过以下方式获得正则表达式支持：
- Enabling CLR (doesn't require instance restart)
- Uploading your CLR functionality (in this case, regex replace)
- 启用 CLR（不需要实例重启）
- 上传您的 CLR 功能（在这种情况下，正则表达式替换）
Using native TSQL, you'll need to define REPLACEstatements for everything you want to remove:
使用本机 TSQL，您需要为要删除的所有内容定义REPLACE语句：
SELECT REPLACE(
REPLACE(
REPLACE(''/ID1:Category1/ID2:Category2/ID3:Category3/'', 'ID1:', ''),
'ID2:', ''),
'ID3:', '')
Regex or otherwise, you need to be sure these patterns don't appear in the actual data.
正则表达式或其他方式，您需要确保这些模式不会出现在实际数据中。
回答by JoshBerke
You can use SQL CLR. Here's an MSDN article:
您可以使用 SQL CLR。这是一篇MSDN 文章：
回答by Gaurav
declare @test1 nvarchar(max)
set @test1='/ID1:Category1/ID2:Category2/ID3:Category3/'
while(CHARINDEX('ID',@test1)<>0)
Begin
select @test1=REPLACE(@test1,SUBSTRING(@test1,CHARINDEX('ID',@test1),CHARINDEX(':',@test1)-
CHARINDEX('ID',@test1)+1),'')
End
select @test1
