CODEWITHSUNDEEP
sql-serverstringfunctiont-sqlextract
Hell.Bent
Hell.Bent

Reputation: 1677

SQL function that extracts strings

From a prior question, this SQL function extracts strings following a set string.

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:

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

Upvotes: 2

Views: 417

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

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.

Upvotes: 2

Andriy M
Andriy M

Reputation: 77687

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

Upvotes: 2

Thomas
Thomas

Reputation: 64645

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.

Upvotes: 0

Related Questions