Function for getting a extension from a filename

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

In .NET, you can use the FileInfo class:

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.

Function

Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.Yes. Check out books on line under UDF.|||In Northwind database I try to create and test the sample UDF shown in BOL as below:
CREATE FUNCTION LargeOrderShippers ( @.FreightParm money )
RETURNS @.OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @.OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @.FreightParm
RETURN

SQ analyser displays the following:
Server: Msg 170, Level 15, State 1, Procedure LargeOrderShippers, Line 18
Line 18: Incorrect syntax near 'RETURN'.

Please advise.
Thanks much.
|||Try this link for all the info you need about UDF (user defined functions), the person who runs the site is a UDF expert. Hope this helps.
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm|||check thislink|||

The syntax for it is :
create function <function name ( param 1 <datatype>,...)>
returns table
as
return
select .....
go

Hope this solves your query...
Cheers
Ajay G

|||Sorry, I missed the "END" at the end. After I add it back, the UDF is created and runs OK.
Thanks much.