SQL function that extracts strings
From a prior question, this SQL function extracts strings following a set string.
开发者_StackOverflow中文版so,
extractAfter("hello this is a Test:12356.jpg reset of line","Test:")
returns: 12356.jpg
It works, but I need to tweak it to consider two other things:
- in addition to whitespaces the string should end when "<" is encounter
- that if no match, I'd like to return null
so,
extractAfter("hello this is a Test:12356.jpg<br> reset of line","Test:")
would also return:12356.jpg
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
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
Rather than try to keep tweaking a string parsing function when T-SQL really does not specialize in text-parsing, I would recommend using Regular Expressions if you are on SQL Server 2005 or newer. You can find many examples on the internet of the exact SQLCLR code for these, or you can download the Free version of the SQL# library (which I wrote) and start using them right away. Here are some examples using your specific situation:
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
In each case, the pattern match starts with the word "Test:" then grabs any non-space characters until it reaches a "<br>" (or a space since it is looking for non-space). If not found due to nothing being between the "Test:" and the "<br>" or if the "Test:" isn't there to begin with, it returns NULL
.
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
I have slightly modified my solution to account for the scenario where the search string happens to be at the end of the input string.
精彩评论