I need a function that returns the extension of a filename, im not so the T-SQL expert so i wanted to ask if this query is ok?
would it be faster to do this as a CLR function?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_GetFileExtension]
(
@.Name nvarchar(256)
)
RETURNS nvarchar(256)
AS
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - 3, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - 2, 3 ));
DECLARE @.i int;
SELECT @.i = 1;
WHILE ( @.i < LEN(@.Name) )
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - @.i, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - @.i + 1, @.i ));
ELSE
SELECT @.i = @.i + 1;
END
RETURN '';
END
return new FileInfo(filename).Extension;
You need to run some tests to see if that's faster than SQL, though...|||I would recommend that you use some other method personally. If part of the consumer is using .NET or if you are using SQL 2k5 then you have Path.GetExtension (a static method that when provided with a filename returns the extension).
|||i tried the clr way (with Path.GetExtension) and it's the faster solution. it's over 10 times faster than the sp, that's much more than i expected...|||Yep. Glad the problem was solved.
No comments:
Post a Comment