HELP...Splitting a string in T-SQL

I have a situation where I am querying the master.dbo.sysaltfiles to return
the path to the datafiles. What I am really interested in is the path...not
the filenames.
Example:
select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
@.CurrentDB
This returns: M:\Microsoft SQL Server\CurrentDB.mdf
What I need is just the path:
Example:
M:\Microsoft SQL Server\
I was looking for something like VB's split string or something to remove
the filename.
What can I do to achieve the desired result?
Thanks!
RonThere are plenty of string functions in T-SQL that will help with this.
Check out REVERSE, CHARINDEX and SUBSTRING.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I am querying the master.dbo.sysaltfiles to
> return the path to the datafiles. What I am really interested in is the
> path...not the filenames.
> Example:
> select @.DataPath = FileName From master.dbo.sysaltfiles WHERE name =
> @.CurrentDB
> This returns: M:\Microsoft SQL Server\CurrentDB.mdf
>
> What I need is just the path:
> Example:
> M:\Microsoft SQL Server\
> I was looking for something like VB's split string or something to remove
> the filename.
> What can I do to achieve the desired result?
>
> Thanks!
> Ron
>|||Excellent. If possible could you give me a sample of how to use them to
achieve what I'm going for?
Thanks,
Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OY9JLgvNGHA.3984@.TK2MSFTNGP14.phx.gbl...
> There are plenty of string functions in T-SQL that will help with this.
> Check out REVERSE, CHARINDEX and SUBSTRING.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23Yp4navNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example
declare @.c varchar(50)
select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
Just replace @.c with your field name
http://sqlservercode.blogspot.com/|||Thank you so much!
I'm under the gun so I needed to "learn" quickly. I appreciate your help!
Ron
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140535404.793871.132130@.g47g2000cwa.googlegroups.com...
> Here is an example
> declare @.c varchar(50)
> select @.c ='M:\Microsoft SQL Server\CurrentDB.mdf'
> select left(@.c,(len(@.C) -CHARINDEX('',reverse(@.c)))+1)
> Just replace @.c with your field name
> http://sqlservercode.blogspot.com/
>|||Hi There,
I hope this help to solve the problem.
Select identity(int,1,1) Seq into Seq From sysobjects
Select * from seq
Select Substring(String,1,Max(seq)) From
(
Select * From Seq ,(
Select 'c:\aa\bb\cc\dd\ee' As String) S
Where substring(String,seq,1) = '\'
) SS Group By String
Drop Table Seq
With Warm regards
Jatinder Singh