How to use the SQL Server RIGHT string function with T-SQL Examples 2005, 2008, R2, 2012, 2014

SQL Server RIGHT function code

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!

 

How to use Transact-SQL DEALLOCATE with Examples – 2005, 2008, R2, 2012, 2014

TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE

Topic: Transact-SQL DEALLOCATE

The purpose of the DEALLOCATE keyword in sql server is to remove a cursor reference.  SQL Server has data structures behind the scenes that are released once DEALLOCATE is executed.

Syntax

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }


Example

-- SAFEGUARD
-- not familiar with the .. syntax below?  check out this blog <a title="dbo .. syntax and the default database" href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/" data-mce-href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/">post</a> 
IF OBJECT_ID('tempdb..#Hobbies') IS NOT NULL
BEGIN
    DROP TABLE #Hobbies
END

-- create the temp table
CREATE TABLE #Hobbies (
	id INT IDENTITY(1,1) NOT NULL,
	article_name NVARCHAR(100)
)

-- fill the temp table with sample data
INSERT INTO #Hobbies (article_name)
VALUES 
('cooking'),('crafts'),('flying'),
('T-SQL'), ('wood carving'),('gardening'),
('reading'),('beer brewing'),('gaming');

SELECT article_name FROM #Hobbies;

-- create the variable we'll use to store data from the FETCH
DECLARE @articleName NVARCHAR(100);

------------------ CURSOR SPECIFIC CODE BEGIN --------------------
-- declare the fast_forward cursor.  notice we are essentially
-- using a SELECT query to build a list to iterate through
DECLARE hobbiesCursor SCROLL CURSOR FOR
SELECT article_name FROM #Hobbies ;

-- now we have to open the cursor and tell it to go
-- acquire (i.e. FETCH) items from the temp table
OPEN hobbiesCursor
FETCH FIRST FROM hobbiesCursor INTO @articleName;
SELECT ('First Hobby Name: ' + @articleName );	
FETCH NEXT FROM hobbiesCursor INTO @articleName;
SELECT ('Second Hobby Name: ' + @articleName );	
FETCH LAST FROM hobbiesCursor INTO @articleName;
SELECT ('Last Hobby Name: ' + @articleName );
FETCH PRIOR FROM hobbiesCursor INTO @articleName;
SELECT ('Second to Last Hobby Name: ' + @articleName ) ;	

CLOSE hobbiesCursor ;
DEALLOCATE hobbiesCursor;

Results

(9 row(s) affected)
article_name
----------------------------------------------------------------------------------------------------
cooking
crafts
flying
T-SQL
wood carving
gardening
reading
beer brewing
gaming

(9 row(s) affected)

----------------------------------------------------------------------------------------------------------------------
First Hobby Name: cooking

(1 row(s) affected)

-----------------------------------------------------------------------------------------------------------------------
Second Hobby Name: crafts

(1 row(s) affected)

---------------------------------------------------------------------------------------------------------------------
Last Hobby Name: gaming

(1 row(s) affected)

-------------------------------------------------------------------------------------------------------------------------------
Second to Last Hobby Name: beer brewing

(1 row(s) affected)

Notice DEALLOCATE is at the very bottom of the code listing.  It’s role is simply to perform memory ‘clean up’.  A LOCAL cursor variable does not have to be deallocated since it freed when the variable goes out of scope (end of the batch, stored procedure, or trigger), however GLOBAL cursors should be freed unless you intentionally mean to reuse the cursor during the course of the entire database connection.  Additionally, If you are planning to reuse a cursor variable multiple times in your scope then you should deallocate the cursor variable as required.

Example SQL

USE AdventureWorks2008;
GO

DECLARE @PersonCursor CURSOR
SET @PersonCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Person.Address;

DEALLOCATE @PersonCursor;

SET @PersonCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Person.EmailAddress;
GO

/* CURSOR IS AUTOMATICALLY DEALLOCATED 
WHEN IT GOES OUT OF SCOPE */

For some of the deeper details of DEALLOCATE you can check out the documentation here.

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

TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE
TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE EXAMPLE CODE

How to Loop using SQL Server Cursor Fetch fast_forward – 2005, 2008, R2, 2012, 2014

how to loop using sql server cursor fetch fast_forward tsql t-sql

In this post, describing how to loop using SQL Server cursor fetch fast_forward, I’m going to give an example of looping through a temporary table using a cursor and call a stored procedure for each iteration. The focus of this post is on the fast_forward cursor which is a shorthand way of saying the cursor is forward only and read only.  The fast_forward cursor type has performance optimizations enabled so this allows the speed to be about as good as possible.  The SQL Server query processing team wrote up a very technical and detailed article on how fast_forward cursors work and it can be found here.  It may come as a surprise that there are cursors that allow other actions as well.  I’ll be covering those in future posts.  On to the example…

The majority of code below is just wiring up code to run the example.  I create a stored procedure and temporary table which will be used by the cursor.  The cursor specific code is found on lines 55-83.

-- CURSOR EXAMPLES DATABASE PREPARATIONS

-- you should change this to whatever database you'd like to run
-- this example in.  I'm using AdventureWorks2008 since many folks
-- may already have it.  
USE AdventureWorks2008;

-- SAFEGUARD - check if the stored procedure exists and if so delete it
IF OBJECT_ID('[uspGiveRecommendationBasedOnFlag]') IS NOT NULL
	DROP PROCEDURE [uspGiveRecommendationBasedOnFlag];
GO

-- create the demo stored proc.  we'll delete it when we're done.		
CREATE PROCEDURE [uspGiveRecommendationBasedOnFlag]
	@articleFlag BIT,
	@articleName NVARCHAR(50)
AS
BEGIN
	-- you would do some actual work here in a real app
	IF(@articleFlag = 1)
		PRINT 'You should publish the article: ' + @articleName;
	ELSE	
		PRINT 'You shouldn''t publish the article: ' + @articleName;
END
GO

-- SAFEGUARD
-- not familiar with the .. syntax below?  check out this blog post 
IF OBJECT_ID('tempdb..#cursorExample') IS NOT NULL
BEGIN
    DROP TABLE #cursorExample
END

-- create the temp table
CREATE TABLE #cursorExample (
	id INT IDENTITY(1,1) NOT NULL,
	flag BIT NOT NULL,
	article_name NVARCHAR(100)
)

-- fill the temp table with sample data
INSERT INTO #cursorExample (flag, article_name)
VALUES 
(1,'cooking'),(1,'crafts'),(0,'flying'),
(1,'T-SQL'), (0,'wood carving'),(1,'gardening'),
(0,'reading'),(1,'beer brewing'),(0,'gaming');

-- create the variables we'll use when inside the the loop
DECLARE @articleFlag BIT;
DECLARE @articleName NVARCHAR(100);

------------------ CURSOR SPECIFIC CODE BEGIN --------------------
-- declare the fast_forward cursor.  notice we are essentially
-- using a SELECT query to build a list to iterate through
DECLARE myCursorSample CURSOR fast_forward FOR
SELECT flag, article_name FROM #cursorExample ;

-- now we have to open the cursor and tell it to go
-- acquire (i.e. FETCH) the NEXT (i.e. first since this is the first call)
-- item in the list
OPEN myCursorSample
FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName ;

WHILE @@FETCH_STATUS = 0 
    BEGIN       
        -- DEBUG
        -- PRINT ('Before stored proc execution cursor value: ' + @articleName ) ;	

		EXEC	[uspGiveRecommendationBasedOnFlag] 
				@articleFlag = @articleFlag,
				@articleName = @articleName;

		-- get the next article from the table using the cursor
        FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName;
    END

CLOSE myCursorSample ;
DEALLOCATE myCursorSample;

------------------ CURSOR SPECIFIC CODE END --------------------

-- CLEAN UP EXAMPLE 
DROP PROCEDURE [uspGiveRecommendationBasedOnFlag];

I tried to make the code as clear a possible with comments but I should point out a few things.  The SAFEGUARD comments are there only to show where I’m doing existence checks before actually performing an action on the database.  This is good practice for things like change scripts where running the same code twice shouldn’t cause errors or break something.  Second, this code is very well documented.  Although this amount of commenting is overboard for most production systems you should try to document why you’re using the cursor and perhaps your justification for doing so.  This should help minimize improper use of cursors in the codebase.  Lastly, while this example stored proc call isn’t doing anything a set based query couldn’t, you can imagine cases where based on values in a table you might have to carry out significant business logic actions which are not conducive to set based operations.

The results of the code execution are below.

(9 row(s) affected)
You should publish the article: cooking
You should publish the article: crafts
You shouldn't publish the article: flying
You should publish the article: T-SQL
You shouldn't publish the article: wood carving
You should publish the article: gardening
You shouldn't publish the article: reading
You should publish the article: beer brewing
You shouldn't publish the article: gaming

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

how to loop using sql server cursor fetch fast_forward tsql t-sql
how to loop using sql server cursor fetch fast_forward tsql t-sql