提取字符串的SQL函数
|
从上一个问题开始，此SQL函数将在设置的字符串之后提取字符串。
所以，
extractAfter(\"hello this is a Test:12356.jpg reset of line\",\"Test:\")
extractAfter(\"hello this is a Test:12356.jpg<br> reset of line\",\"Test:\")
create function dbo.extractAfter(@full nvarchar(max), @part nvarchar(max))
returns nvarchar(max) with returns null on null input as
begin
return ltrim(stuff(left(@full,charindex(\' \', @full + \' \', charindex(@part,@full)+1)), 1, charindex(@part,@full)+datalength(@part)/2 -1, \'\'))
end
go
没有找到相关结果
已邀请:
3 个回复
凄嘛姥
CREATE FUNCTION dbo.extractAfter (@full nvarchar(max), @part nvarchar(max)) RETURNS nvarchar(max) WITH RETURNS NULL ON NULL INPUT AS BEGIN RETURN ( SELECT LEFT(p, PATINDEX(\'%[ <]%\', p + \' \') - 1) FROM (SELECT p = STUFF(@full, 1, NULLIF(CHARINDEX(@part, @full), 0) + LEN(@part) - 1, \'\') ) s ) END僻朵庙惩竣
SELECT SQL#.RegEx_CaptureGroup(\'hello this is a Test:12356.jpg<br> reset of line\', \'Test:([^ ]+)<br>\', 1, NULL, 1, -1, \'\') -- 12356.jpg SELECT SQL#.RegEx_CaptureGroup(\'hello this is a Test:<br> reset of line\', \'Test:([^ ]+)<br>\', 1, NULL, 1, -1, \'\') -- NULL SELECT SQL#.RegEx_CaptureGroup(\'hello this is a T:12356.jpg<br> reset of line\', \'Test:([^ ]+)<br>\', 1, NULL, 1, -1, \'\') -- NULL\”（或空格，因为它正在寻找非空格）。如果由于\“ Test：\”和\“
\”之间什么都不存在而找不到，或者如果\“ Test：\”开头不存在，则返回
。陷牡彭拈峰
Declare @TestString varchar(max) Declare @TestSearch varchar(max) Set @TestString = \'hello this is a Test:12356.jpg<br> reset of line\' Set @TestSearch = \'Test:\' Select Case When CharIndex( @TestSearch, @TestSTring ) = 0 Then \'\' When Left(Reverse( Z.Value ), Len(Z.Value)) = Reverse( Z.Value ) Then Z.Value Else Substring( Value, 1, PatIndex( \'%[<> ]%\', Value ) - 1 ) End From ( Select Substring( @TestString , CharIndex( @TestSearch, @TestSTring ) + Len(@TestSearch) , Len(@TestString ) ) As Value ) As Z