CODEWITHSUNDEEP
sql-serverregext-sqlsql-server-2005pattern-matching
Hell.Bent
Hell.Bent

Reputation: 1677

SQL Server 2005 function to extract a substring match following a set string

Anybody have a nice tight and efficient SQL Server function that will return the fist string (terminated by a whitespace) following the first match of a given string.

I've got some code, but it's real ugly and probably slow.

For example, in In test 12545 file:x12545.jpg appears to be good given file: would return x12345.jpg

Thanks.

Upvotes: 1

Views: 946

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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: 1

Related Questions