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

Question

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.

RichardTheKiwi · Accepted Answer · 2011-03-25 10:40:50Z

1

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

answered Mar 25, 2011 at 10:40

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hell.Bent Over a year ago

Seems to work, but need some help tweaking it. Can we return null if no match? Also, can we include < as delimiter (due to possible hidden html tags) so that file:x12567.jpg<br> would not pick up the tag. Thanks!

Andriy M Over a year ago

datalength(@part)/2 could also be length(@part + '.') - 1. The former may be faster, while the latter is more universal (can work both with nvarchar and varchar).

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related