“Fun” with T-SQL String Functions

… and when I mean “fun” I mean “staring blankly at the screen while I figure out what I want to do and how to do it”.

I’ve written about string function many times before, and have used them extensively in many scripts I have written, but there’s always something new that comes up. Today, I wanted to get the file location of where the database files are stored and create some more files using dynamic SQL.

use [AdventureWorks2014]

select * FROM [sys].[database_files]

There’s plenty of examples online to get the physical file name (it’s worth stressing at this point that the name and physical_name of a file can be different), and in fact even in some scripts I have written myself I’ve done this:

select RIGHT(physical_name, LEN(REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name))-1)))) FROM [sys].[database_files]

This is what the query looks like when indented: I found it easier to read it like this.

STRING_FUNCTION_1Lets step through this, because string functions always throw me:

  • RIGHT takes an expression (in this case [physical_name]) and the right most characters of the string based on a length. So LEN is used to get the starting point of a character in the string. In this case, we want the value to the last ‘\’ that occurs in the string from the right of the string (remember we are counting from the RIGHT, not left.) This is so that we can strip everything before the last index of ‘\’ in the string. As there is no “LastIndexOf” function in T-SQL, and I really wish there was, then we need to get the length of the string from the last character to the last ‘\’. And to get this, we use SUBSTRING to return us part of the string first by reversing the [physical_name], using a start point of 1, and use CHARINDEX to get the starting point of the ‘\’ by reversing [physical_name] again. This is not a reversal of the reversal remember, we are reversing so that we can use CHARINDEX. From this point we subtract 1, which is the ‘\’ included in the CHARINDEX expression. This gives us the file name.

Simple right? Now, if there was a LastIndexOf function in T-SQL, most of this could be removed. Sadly, we have to get creative in T-SQL.

But this does not solve my issue: I want everything to the LEFT of the last ‘\’ not the right. There’s probably several ways of achieving this, but I found a method below I’m happy with:

SELECT RIGHT(physical_name, LEN(REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name)) - 1))))
FROM [sys].[database_files]
WHERE type = 0

Again, indented makes for easier reading:


  • Now we can use LEFT which will take the left most characters up to a certain values, but this time we want to subtract the starting point of the last ‘\’ counted from the RIGHT. (If this isn’t entire clear, when we count from the right we count from right to left as 1,2,3,4… etc, the same way we do from the left. We always count up, it’s the direction of which way we’re counting form that is the distinction.) So in the same way as the previous method we use LEN to retrieve the value of the last ‘\’ counting from the right by reversing the [physical_name], starting at the first character and using CHARINDEX and REVERSE to get the starting point of ‘\’  before subtracting 1 to remove the ‘\’.

I actually really enjoy working with string functions because it’s a challenge, but you really need to know all the string functions that are available in a given language, and wheras C# has many more an Intellisense to really help you out, T-SQL has fewer functions and no Intellisense to help you out (or at least, no Intellisense at this level)

That’s it for today, happy scripting!


Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s