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:
- Reverse the full path of the file name
- Use the LEFT function to return all of the characters left of the first instance of “\”
- Reverse the string back to normal orientation
SELECT name AS 'Database Name' , REVERSE(physical_name) 'Path Reversed' , REVERSE(LEFT(REVERSE(physical_name), 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.