Saturday, May 25, 2019 ..:: Home ::.. Register  Login

Peter Henry on Facebook  Peter Henry on LinkedIn  Peter Henry on Twitter

All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
   TechTidBits (Blog)  

Extracting paths\directories\filenames from SQL Server using sql fields

Mar 23

Written by:
Monday, March 23, 2009 8:46 PM  RssIcon

Have you ever wanted to extract a path or directory name from a field in SQL Server?  I found myself looking at that today and I found a cool and nifty trick.  Read on for more information.

Today at work I was looking at creating Database Snapshots in SQL Server.  To do this successfully :> I needed a database's logical name and the path where it was.  At this point, you might be asking, "logical name?"  WTF?  GOOD question!  Don't worry, I'm getting to the extraction part in a sec.

Physical Name: the name of the file on the hard drive, the actual *.mdf or *.ldf file.

Logical Name: the unique name SQL Server uses to reference the db in t-sql scripts, please notice, this does not always match what you see in Management Studio's Database tree list.

Most of the time, these will be the same, but sometimes when you're moving dbs around, creating new ones for testing/debugging/development purposes, they get changed.  The following illustrates this.  There is a DB called ODNCStudyGroup and that's the name I see in Management Studio database list, but when t-sql requires the logical name, I need to use WebStudyGroup.  Below is the sql and result sets after I run the sql.

Physical and Logical DB names

Hhhmm there an easier way to get the Logical name?  Yup.  Look at the database properties.

 Database Properties to get Logical name

Now that we know how to get the logical name in t-sql, we need to work on extracting the dbs filename and path.  The trick to extracting the path and filename is to reverse the string, find the location of the slash, then doing some math manipulation and you're gold!  That's it.  Once I read the blog about using reverse, everything just fell into place.  Next is the sql I used.  I added in the filename for a bonus.

 T-sql for snapshot

Why does this work?  Magic!  No, just kidding.  The secret is in t-sql for string manipulation and getting the proper character indexes/positions to do the string math on.  Once you reverse the string for the db path (since the table is a system table, you can pretty much trust the slash is a backwards slash and not have to do too much error detection/correction) taken together with the length of the string/field data, you have the index/position of the slash separating the path from the filename.

I hope you can use this little string manipulation trick.  And if you have any others, please drop a comments!  I would love to hear about.  Now go grab a coffee and get coding! :>

Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Add Comment   Cancel 
Copyright 1999-2012 by   Terms Of Use  Privacy Statement