Tag: CHARINDEX

The SQL Server RIGHT string function gives us the ability to return a given number of characters from a string on its rightmost side.  For example, say we have the text ‘Bill Jones’.  We can use the RIGHT function to return ‘Jones’ without having to parse the string from the beginning.  Let’s look at some code…

RIGHT ( string , int )

The code above is the syntax of the RIGHT function.  The first parameter is a string and the second is an integer letting sql server know how many rightmost characters you want from the string in the first parameter.  Let’s go back to our initial example from above…

SELECT RIGHT ('Bill Jones', 5);

The result of this query are:

Jones

Pretty simple stuff.  To use the SQL Server RIGHT function in a more practical way we might use it with a table.  So, let’s define a table called Document and fill it with some sample filenames.  We’ll then use the RIGHT and LEFT functions to help us fill in the extension for each file.

-- create the document table
CREATE TABLE Document (
Filename VARCHAR(50),
Extension VARCHAR(10)
);

-- insert the demo rows into the document table
INSERT INTO Document ([Filename]) VALUES ('family.jpg');
INSERT INTO Document ([Filename]) VALUES ('lessons.docx');
INSERT INTO Document ([Filename]) VALUES ('schemas.xml');
INSERT INTO Document ([Filename]) VALUES ('report.ps');
INSERT INTO Document ([Filename]) VALUES ('puppies.report');
INSERT INTO Document ([Filename]) VALUES ('secret.password');

-- get extension column size - this is a bit advanced since 
-- we are looking at the table's meta data to set our max
-- column size.  the query below works whenever you don't 
-- know the column size ahead of time and need to find it
-- dynamically.  
DECLARE @extensionColumnSize INT;
SELECT @extensionColumnSize = character_maximum_length    
    FROM information_schema.columns  
    WHERE TABLE_NAME = 'Document' AND COLUMN_NAME = 'Extension';

-- get the extension for each filename
DECLARE @i INT;
SET @i = 1;
WHILE @i < @extensionColumnSize
BEGIN
   UPDATE Document
   SET [Extension] = RIGHT([Filename], @i-1) FROM Document a
   WHERE (LEFT(RIGHT([Filename], @i),1)) = '.';

   SET @i = @i + 1;
END

-- table extensions should be filled in now
SELECT * FROM Document;

-- clean up by dropping/deleting table
DROP TABLE Document;

The first part of the example simply creates the Document table and fills it with six rows of data.  The second part is the juicy stuff.  First we find the maximum possible length of  an extension by getting the size of the Extension column from the database meta data.  Next, we loop from 1 to Extension column size which, in this case, is 10.  Each time through the loop we get the rightmost @i letters and check to see if a period is on the leftmost part of the string.  If it is a period then we update the Extension column with the rightmost @-1 characters (we don’t save the period).

I’ll admit that the example is not very simple.  However, you could ‘simplify’ the code above  as above but replacing lines 15-35 with the following…

UPDATE Document SET [Extension] = RIGHT([Filename], CHARINDEX('.', REVERSE([Filename]))-1);

It’s actually quite funny how much less code it takes.  The good thing with that one liner is that we don’t have to loop and deal with all kinds of variables and system tables but we do have to know about CHARINDEX and REVERSE…two more sql server functions.  I’ll be discussing those in later posts.  I think the first example, while perhaps a bit contrived, does provide some code that you might be able to repurpose for your own uses.  I cheated a little as well by also using the SQL Server LEFT function but you’ll find that they can be used together quite powerfully.

The results of the running code (either method) are below.

Filename                                           Extension
-------------------------------------------------- ----------
family.jpg                                         jpg
lessons.docx                                       docx
schemas.xml                                        xml
report.ps                                          ps
puppies.report                                     report
secret.password                                    password

So thats the SQL Server Right function and hopefully the example above will give you some ideas of your own on how to use it.

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

Happy coding!