August 2013 - ExampleSQL.com

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 an 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.

SELECT REVERSE(1234567890);

------------
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.

How many times do we as SQL developers have to declare and set variables? I recon that this occurs quite frequently, perhaps a million times a day…well, maybe not that many but it FEELS like it sometimes. Prior to SQL Server 2008, we were forced to declare the variable in one statement and then set it in a following statement. We got used to it but it was a constant annoyance for those of us to have better things to do then fiddle with SET statements every other line.

With SQL Server 2008, 2012 and above, we can now declare and set variables in a single statement.

-- Old clunky way of doing things prior to SQL Server 2008
DECLARE @myVar VARCHAR(100);
SET @myVar = 'Some Value';

-- New, amazing way of declaring variables
DECLARE @myVar VARCHAR(100) = 'Some Value';

We can see above the old, nasty way we are all familiar with. Then we see the amazingly simple and efficient way we can do it now with SQL Server 2008 and above. I bet we save on average of 10-12 keystrokes. Multiplying that times the million times we have to do this a day and you’ll see we end up with quite a significant savings of work. Your life just got a bit better!

Power on SQL Devs!

Here is the full technical reference for declaring a setting variables in SQL Server 2014.

Valid for SQL Server 2008, 2008 R2, 2012, 2014.

Month: August 2013

SQL Server REVERSE String Function with Examples 2008, R2, 2012, 2014

DECLARE and SET Variables in one line SQL Server 2008, R2, 2012, 2014