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.

Full-Text Search

I am trying to create a full text index on a column in a table and the data type is Image, but the TYPE COLUMN FILE EXTENSION is in another table. there is no way of creating the type in the same table. the wizard will not allow me to pick the data type field from another table. is there a work around this issue?

Emadkb

Adding a column to the table is not an option?|||It is a fram work table and making any changes now would be impossible. Do you see any other solutions to this issue. I really appreciate your quick response. Emadkb|||If you are using SQL 2005, you can make an indexed view and build fulltext index on the view. However that do means you dup the storage for the image columns.|||Thank you so much....I was wishing there is another solution. emadkb|||

hey Feng Tian
I hope you could help me in FullText Search. No one else seems to be knowing about Fulltext Search.
I'm able to create Full text indexing catalogs, populate them etc.. when i login directly from the server using Sql Server Enterprise manager but when I try to use the same login (db owner) remotely from some other machine using SQL Server Enterprise Manager, I see disabled(stopped) Full-text Service and all full-text features disabled. So does it mean i cannot populate or create catalogs remotely using sql server enterprise manager.
I have tried this on two different servers and then accessing from two different remote/client machines. But the problem is there any way.

Server: Mssql 2000 server
Remote/Client system: Win XP service Pack2
Access Software: Microsoft Sql Server 2000 Enterprise Manager.

Thanks
Sattva

|||

Sattva,
The behievor you are experiencing with your SQL Server 2000 Enterprise Manager (EM) is typical and expected. The EM cannnot "see remote" services such as the "Microsoft Search" (MSSearch) service on remote servers when accessed across the internet or the server is registered as an IP address. However, SQL FTS is still functional and you can use the system stored procedures to remotely manage and populate the FT Catalogs, for example:

To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
--
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index & Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog, Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END

EXEC sp_fulltext_catalog 'PubInfo', 'start_full' -- "Full Crawl"
Wait for crawl to complete
--
Confirm above results with:
--
SELECT pub_id, pr_info
FROM pub_info WHERE CONTAINS(pr_info, '"book*"')

See my blog entry "SQL Server 2000 Full-Text Search Resources and Links" for more SQL 2000 FTS information.

Hope that helps,
John

SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||

Thanks John. It was a great help. Now I hav it all working.

Cheers mate.