替换为通配符,在 SQL 中 [英] Replace with wildcard, in SQL
问题描述
我知道 MS T-SQL 不支持正则表达式,但我需要类似的功能.这是我正在尝试做的:
I know MS T-SQL does not support regular expression, but I need similar functionality. Here's what I'm trying to do:
我有一个存储面包屑的 varchar 表字段,如下所示:
I have a varchar table field which stores a breadcrumb, like this:
/ID1:Category1/ID2:Category2/ID3:Category3/
每个类别名称前面都有其类别 ID,用冒号分隔.我想选择并显示这些面包屑,但我想删除类别 ID 和冒号,如下所示:
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:
/Category1/Category2/Category3/
所有之间前导斜杠 (/) 到并包括冒号 (:) 的内容都应该去掉.
Everything between the leading slash (/) up to and including the colon (:) should be stripped out.
我无法选择提取数据,在外部对其进行操作,然后重新插入表中;所以我试图在 SELECT 语句中完成这个.
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.
这能做到吗?
谢谢大家 - 杰
推荐答案
我认为最好的办法是使用递归用户定义函数 (UDF).我在此处包含了一些代码,您可以使用这些代码传入字符串以实现您正在寻找的结果.
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.
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
这篇关于替换为通配符,在 SQL 中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!