Recently I had a conversation with a colleague that wasn’t aware of the REVERSE function in SQL Server.  I had sent them a code snippet and they inquired as to what the purpose of the function was.  Essentially, this function that reverses any string value.

For example, the phrase:

The brown dog jumped over the lazy fox

reversed looks like this

xof yzal eht revo depmuj god nworb ehT

Or in Management Studio

Awesome.  Where can I use this?  I use this function when I need to get file names for data or log files of a database.  These are the steps to do this:

  1. Reverse the full path of the file name
  2. Use the LEFT function to return all of the characters left of the first instance of “\”
  3. Reverse the string back to normal orientation
SELECT  name AS 'Database Name' ,
        REVERSE(physical_name) 'Path Reversed' ,
                     CHARINDEX('\', REVERSE(physical_name)) - 1)) AS 'File Name'
FROM    sys.master_files;

In Management Studio,

Voilá!!  We have the file names.  This is also helpful whenever you need to find the tail end of a string that has some type of delimiter.


© 2017, John Morehouse. All rights reserved.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?