The SQL Server REVERSE string function can be quite useful if you need to return a string or sequence in reverse order. It can also be quite powerful when used in combination of other SQL keywords (CHARINDEX, RIGHT). We’ll start by looking at some of the simple uses.
[Sarcasm to follow]
So say you’re and android and you’ve always dreamed of reversing your name and have gone your whole life up to this point just waiting to be able to do it in SQL. Today is your lucky day! The code below will magically turn your name into its REVERSE version.
SELECT REVERSE('COMMANDER DATA');
-------------- ATAD REDNAMMOC (1 row(s) affected)
Fantastic. This could also assist you if you’re anticipating getting pulled over by the cops for a sobriety test and can’t quite remember how to say the alphabet backwards.
REVERSE can also flip number sequences! This works, of course, because SQL Server converts the numbers to a varchar first.
------------ 0987654321 (1 row(s) affected)
Ok. Ok. Not impressed I see. Then perhaps you’re a word nerd and are big into palindromes. We can use the SQL Server REVERSE function to help us determine if a word is a palindrome like so…
DECLARE @palindromeTest VARCHAR(100) = 'RACECAR'; IF(@palindromeTest = REVERSE(@palindromeTest)) SELECT 'THIS IS A PALINDROME!'; ELSE SELECT 'THIS IS NOT A PALINDROME';
-------------------- THIS IS A PALINDROME (1 row(s) affected)
Note that in SQL Server 2008+ you can declare and set a variable in one line.
Still not impressed. Maybe you are a serious nerd and are into DNA sequencing. Often times you’ll need to find the reverse, complement, or reverse-complement of a DNA sequence. While REVERSE won’t help you with the complement portion of the problem, it will certainly help you reverse your sequence.
-- REVERSE A DNA SEQUENCE DECLARE @dnaSequence VARCHAR(100) = 'GGGatttataaaaaata'; SELECT REVERSE(@dnaSequence);
----------------- ataaaaaatatttaGGG (1 row(s) affected)
Pretty amazing stuff.
Now, going for broke, I’ll be providing a few more complex examples of REVERSE and a useful UDF (user defined function) to make things a bit more convenient.
The last couple of scenarios I want to cover is when you need to pull a filename off the end of a URL and full or absolute path. The technique is identical for all cases as shown below. I incorporated both examples into one to show the similarities.
-- GET THE FILENAME FROM URL -- GET THE FILENAME FROM A FULL PATH DECLARE @URL VARCHAR(1000); DECLARE @FULLPATH VARCHAR(1000) DECLARE @URLFILENAME VARCHAR(100); DECLARE @FULLPATHFILENAME VARCHAR(100); SET @URL = 'HTTP://EXAMPLESQL.COM/SQL-RESOURCES/RESOURCE.PHP'; SET @FULLPATH = '/HOME/FTP/CLIENTS/ACME/TEST.TXT'; -- THESE ARE EXACTLY THE SAME! -- YOU MIGHT MAKE A USER DEFINED FUNCTION (UDF) TO DO THIS IF YOU PLAN ON USING IT A BUNCH -- BUT BE AWARE THAT A CALL TO A UDF DOES CAUSE A PERFORMANCE DECREASE SET @URLFILENAME = RIGHT(@URL, CHARINDEX('/', REVERSE(@URL))-1); SET @FULLPATHFILENAME = RIGHT(@FULLPATH, CHARINDEX('/', REVERSE(@FULLPATH))-1); SELECT @URLFILENAME; SELECT @FULLPATHFILENAME; GO
------------ RESOURCE.PHP (1 row(s) affected) --------- TEST.TXT (1 row(s) affected)
Lastly, I wanted to show everyone a helpful UDF that will essentially encapsulate the magic above into a single callable function.
IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id = OBJECT_ID(N'udfGetFilenameFromPathOrUrl') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION udfGetFilenameFromPathOrUrl; GO CREATE FUNCTION udfGetFilenameFromPathOrUrl( @PATHVAR VARCHAR(1000), @DELIMITER CHAR(1) ) RETURNS VARCHAR(200) BEGIN RETURN RIGHT(@PATHVAR, CHARINDEX(@DELIMITER, REVERSE(@PATHVAR))-1);; END GO
And using the function…
DECLARE @URL VARCHAR(1000) = 'HTTP://EXAMPLESQL.COM/SQL-RESOURCES/RESOURCE.PHP'; SELECT dbo.udfGetFilenameFromPathOrUrl(@URL, '/');
------------- RESOURCE.PHP (1 row(s) affected)
So there you have it. The SQL Server REVERSE function can be useful as we can see from the examples above. Be careful how you wield this power though. 😉
Valid for SQL Server 2008, 2008 R2, 2012, 2014.