String manipulation using sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String manipulation using sql

Author

Topic

olily
Starting Member

37 Posts

Posted - 2002-05-09 : 04:01:50

I have a column with varchar datatype in my table. The column is used to store UNC path for files. For example:

\\servername\foldername\filename.abc

How can I construct a sql query to return only filename.abc??
The servername, foldername and filename.abc are varies in length and file can be in any extension.
I'm using sql server 7.0 and vb6.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-09 : 04:18:18

Olily,

SQL Server has a variety of string manipulation functions.. Look them up in BOL..

This should get you started


DECLARE @S VARCHAR(255)
SET @S = '\\servername\foldername\filename.abc'
SELECT REVERSE(LEFT(REVERSE(@S),CHARINDEX('\',REVERSE(@S))-1))

DavidM

"SQL-3 is an abomination.."

olily
Starting Member

37 Posts

Posted - 2002-05-09 : 04:21:07

Thanks!!

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-09 : 04:26:05

in SQL:
select @filename ='\\servername\foldername\filename.abc'
select right(@filename, CHARINDEX ( '\' , reverse(@filename) ) -1)

in VB6.0:
use the GetFileName method of FileSystemObject

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources