SELECT Archives - ExampleSQL.com

August 4, 2013November 18, 2015

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

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.

June 19, 2013August 17, 2013

How to send a variable to a sql script from a batch file or command line with sqlcmd -v 2008, R2, 2012, 2014

Topic: sqlcmd variable from a batch file to a sql script

Scripting is a powerful way to automate your systems. It should come as no surprise to discover that SQL Server provides a way for developers to send in parameters to a sql script using a batch file via the sqlcmd utility (for SQL Server 2005 and later). sqlcmd is a very versitile utility and I’ll be covering more of its uses in other posts but we’re focusing on sending a variable (possibly more than one) to a sql script either in a batch file or straight from the command line. So without further ado, let’s take a quick look at the syntax…

sqlcmd -v <name of variable>=<value> -v <name of variable>=<value>

This first example is showing the basic idea around passing multiple variables in on the command line using the -v switch followed by the variable name equals value. To be practical, you might need to use a few other switches in order to tell sqlcmd the server you want to connect to ‘-S’ followed by the user (-U) and password (-P). The ‘-S’ is required for SQL Express and the ‘-U’ and ‘-P’ might be required if you’re using SQL Server authentication. Last but not least is the ‘-i’ which tells sqlcmd that there are input file(s) to follow. Yes, you can have more than one of these as well and if you list more than one file they’ll be executed in order.

sqlcmd -S <sql server instance name> -U <username> -P <password> -v <name of variable>=<value> -i <your script.sql>[,file2...]

To really bring this home to roost we also need to know how to access the variable once it has been passed in to our sql script. This is done by using the following…

$(variable)

So, now we’ve defined all the essentials let’s put it all together to make something that actually works.

First, we’ll create an extremely simple sql script named script.sql and place this code in it…

SELECT $(path);

Next, lets create a file named batchfile.bat and place the following inside of it…

sqlcmd -S EXAMPLESQL-COM-PC\SQLEXPRESS -v path="'C:\test\test'" -i script.sql,script.sql

From the command line (assuming both files are in the same place) we execute the batch file…

------------
C:\test\test
(1 rows affected)
------------
C:\test\test
(1 rows affected)

A couple of final notes. I passed the same script file in twice just to show you what that look like. There shouldn’t be any spaces between the files. Also, the dealing with paths specifically, you’ll need to make sure you quote them properly like I did above or else they won’t work properly. You’ll need to change the -S string to your sql server instance name if you want to give this a try.

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

Happy coding!

January 21, 2013August 17, 2013

Basic, Simple, Useful and Practical Examples of T-SQL SELECT 2005, 2008, R2, 2012, 2014

Topic: T-SQL SELECT (This article is about MS SQL Server)

If you haven’t installed SQL Server yet then you’ll need to get it here.

Data in your database is stored in a structure called a TABLE. Tables have columns (hopefully with useful names). On the surface, a table looks alot like an Excel spreadsheet and that can be a useful way to think about them when you’re just starting out. The best way to learn about the SELECT command is by doing so let’s jump right into some examples.

Example

Let suppose we have a database called AdventureWorks2012 (an example database available from Microsoft for free here) and a table called Person.Person. It looks like this

Person.Person Table Definition

You can imagine the table looking something like this under the covers…

BusinessEntityID	FirstName	MiddleName	LastName	Suffix	EmailPromotion	Demographics	rowguid	ModifiedDate
1	Billy	The	Kid	Jr.	Business	18-25	92C4279F-1207-48A3-8448-4636514EB7E2	2013-08-16 00:00:00.000
2	Gertude	J	Miller	NULL	Consumer	65+	F2D7CE06-38B3-4357-805B-F4B6B71C01FF	2012-11-23 00:00:00.000

As you can see it has the following columns: BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate. In the image, you’ll see after the column name is some additional information in parenthesis. This extra information is describing the type of data that particular column can hold. I won’t get into it in this post but basically it is good database design to ensure that your data is consistent. Therefore, you only put numbers in columns that take numbers and you only put dates in columns that accept dates and so on and so forth.

The quickest and dirtiest way to get information from a table is to execute a SELECT query like this:

SELECT * FROM AdventureWorks2008.Person.Person

The asterisk (star) basically says to grab all columns from the table.

Results:

BusinessEntityID	PersonType	NameStyle	Title	FirstName	MiddleName	LastName	Suffix	EmailPromotion	AdditionalContactInfo	Demographics	rowguid	ModifiedDate
1	EM	0	NULL	Ken	J	Sánchez	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	92C4279F-1207-48A3-8448-4636514EB7E2	1999-02-08 00:00:00.000
2	EM	0	NULL	Terri	Lee	Duffy	NULL	1	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	D8763459-8AA8-47CC-AFF7-C9079AF79033	1998-02-24 00:00:00.000
3	EM	0	NULL	Roberto	NULL	Tamburello	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	E1A2555E-0828-434B-A33B-6F38136A37DE	1997-12-05 00:00:00.000
4	EM	0	NULL	Rob	NULL	Walters	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	F2D7CE06-38B3-4357-805B-F4B6B71C01FF	1997-12-29 00:00:00.000
5	EM	0	Ms.	Gail	A	Erickson	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF	1998-01-30 00:00:00.000

I’ve only shown the first five rows of data that were returned from the database but there are actually 19972 rows that are returned if you run the query as it is written. While the query if fast to write, it isn’t always the best choice since it causes a lot of data to have to be processed and unless you actually need every column you should choose to select only the required columns for your application. For example, suppose you only wanted the first and last name of the people in your table. Here is the SELECT query:

SELECT FirstName, LastName FROM AdventureWorks2008.Person.Person

Results

FirstName	LastName
Syed	Abbas
Catherine	Abel
Kim	Abercrombie
Kim	Abercrombie
Kim	Abercrombie
Hazem	Abolrous
Sam	Abolrous
Humberto	Acevedo
Gustavo	Achong
Pilar	Ackerman
Pilar	Ackerman
Aaron	Adams
Adam	Adams
Alex	Adams
Alexandra	Adams

I’ve only shown the first 15 names but this query returns the same number of rows as the previous query 19972. All we’ve done is reduce the columns to the necessary ones. What if we only wanted the first 100 rows? Simple:

SELECT TOP 100 FirstName, LastName FROM AdventureWorks2008.Person.Person

Notice the TOP 100 right after the SELECT command. That tells SQL Server to only pull the first 100 records. What if we wanted to sort by FirstName? This requires some new keywords ORDER BY.

SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName;

I’m selecting the first 15 rows. The default sort order is ascending. Here are the results:

FirstName	LastName
A.	Leonetti
A.	Wright
A. Scott	Wright
Aaron	Adams
Aaron	Alexander
Aaron	Allen
Aaron	Baker
Aaron	Bryant
Aaron	Butler
Aaron	Campbell
Aaron	Carter
Aaron	Chen
Aaron	Coleman
Aaron	Collins
Aaron	Con

What if you want to sort descending? Use the keyword DESC (which stands for descending). There is a keyword for ascending as well…ASC.

SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName DESC;

Ok folks, those are the essentials of the SQL Server SELECT command. I’ve really only touched the surface of the capabilities of the SELECT command but the goal of this post was to expose you to the basics. I’ll be writing more on the deeper details later.

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

January 12, 2013January 18, 2013

How to Declare and Use the MySQL DECIMAL Data Type

What is a MySQL DECIMAL? Simply put, it is just a number that can have a decimal in it (2012.56 and -2013.0034 are examples). Let’s take a look at how to declare a MySQL DECIMAL.

DECIMAL(M, D)

M is the maximum number of digits (IE precision) and has a range from 1 to 65 (Take note that versions of MySQL prior to 5.0.3 allowed the range from 1 to 254). This is the total number of digits INCLUDING decimal digits.

D is the number of digits to the right of the decimal point (IE scale) and has a range from 0 to 30 AND cannot be larger than M.

Why use a MySQL DECIMAL?

You would generally use a decimal type when you need to store exact fractional values like money. It doesn’t suffer from the rounding errors of other number types in MySQL.

How do you use MySQL DECIMAL?

Here is some example mysql code…

CREATE DATABASE IF NOT EXISTS Testing;
USE Testing;
CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
SELECT * FROM WeightCalculation;
DROP DATABASE Testing;

Results

WeightCalculationID|Weight
1	54.34450
2	928.23017
3	982.57467

So let walk through this code and see what is going on. To start, I create our Testing database if it doesn’t exist. Then I create a table called ‘WeightCalculation’ and give it two columns ‘WeightCalculationID’ and ‘Weight’ in order to store some simple data. Notice that column ‘Weight’ has been declare a DECIMAL of total length 10 digits and up to 5 decimal places. Next, I insert two rows of data into the table. Just for fun I calculate the sum of all the Weight column values and store them in a user-defined variable named @a then insert the sum into another row of the table. Lastly, I perform a SELECT operation on the table to get it’s contents. To clean up I drop the Testing database.

For further information check here. Check here for more examples of precision math.

October 22, 2012November 18, 2015

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.

October 9, 2012January 21, 2013

dbo .. syntax and the default database

In this post I’m going to discuss sql server’s dbo .. syntax. Truth be told, it isn’t rocket science. ‘..’ can be used as a replacement for dbo when referring to the default schema in a tsql query. If you need to access the default database frequenty, the dbo .. syntax shortcut can greatly increase your efficiency. If you don’t use the dbo syntax very often then this may not help you too much but it does give you another way to brag to your programmer friends about how awesome you are.

In the following example I’ve made the assumption the default database is AdventureWorks2008. Your default database is likely to be different. To learn how to find the default database for a given login please see the post here.

SQL

SELECT * FROM AdventureWorks2008.dbo.AWBuildVersion; -- if the default schema is AdventureWorks2008 then -- the following is the same as the fully qualified -- table name given on line one. SELECT * FROM ..AWBuildVersion;

Output

SystemInformationID Database Version          VersionDate             ModifiedDate
------------------- ------------------------- ----------------------- -----------------------
1                   10.50.91013.00            2009-10-13 00:00:00.000 2009-10-13 00:00:00.000

(1 row(s) affected)

SystemInformationID Database Version          VersionDate             ModifiedDate
------------------- ------------------------- ----------------------- -----------------------
1                   10.50.91013.00            2009-10-13 00:00:00.000 2009-10-13 00:00:00.000

(1 row(s) affected)