sql string - 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)

sql string

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-03-15 : 17:28:38

In sql I can assign a network share to a variable as follows:

set @tableName = 'F:\data\test\textFile.txt'

I would like to get the name of the .txt into a separate variable. i.e.

set @Filename = textFile

How is this done please?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 17:52:14

[code]select substring(@tableName, len(@tableName) - charindex('\', reverse(@tableName)) + 2,
charindex('\', reverse(@tableName)) - charindex('.', reverse(@tableName)) - 1)[/code]

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-03-15 : 18:10:05

Hi,
There is an error:
Invalid length parameter passed to the SUBSTRING function.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 18:38:29

Post the sample data and cause this error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-15 : 19:05:13

khtan's code works fine for me. fmardani, copy the below code in Query Analyzer and let us know what you get.


declare @tableName varchar(50), @fileName varchar(50)
set @tableName = 'F:\data\test\textFile.txt'

select @fileName = substring(@tableName, len(@tableName) - charindex('\', reverse(@tableName)) + 2, 
                charindex('\', reverse(@tableName)) - charindex('.', reverse(@tableName)) - 1)

print @fileName

Just to be sure, is this for MS SQL Server?

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 01:44:44

"Invalid length parameter passed to the SUBSTRING function."
This could be cause by tablename does not contain the '\' character

select	[tableName], [fileName] = substring(tableName, len(tableName) - charindex('\', reverse('\'+ tableName)) + 2, 
                		charindex('\', reverse('\'+ tableName)) - charindex('.', reverse(tableName)) - 1)
from
(
	select	'F:\data\test\textFile.txt' 	as tableName union all
	select	'F:\data\test\textFile'		as tableName union all
	select	'F:\data\textFile.txt'		as tableName union all
	select  'textFile.txt'			as tableName
) as data

Result :
tableName                 fileName                  
------------------------- ------------------------- 
F:\data\test\textFile.txt textFile
F:\data\test\textFile     textFile
F:\data\textFile.txt      textFile
textFile.txt              textFile

(4 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources