ROW_NUMBER – How To Use It

Facebooktwitterredditpinterestlinkedinmail

ROW_NUMBER is a function built-in to SQL Server that will return a row number for each record in your result set. You can further change resulting row number to reset the row number based on some value in the result set. I will show you examples of both below.

ROW_NUMBER Basics
To show the row number in SQL Server, you need to use the ROW_NUMBER function. This function is broken down in to two parts.

  • PARTITION BY – If you supply this parameter, then the row number will reset based on the value changing in the columns supplied. This is kinda like using a GROUP BY.
  • ORDER BY – This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.

 
With this function I think examples will help paint the full picture.

In the following query, the results will show a row number with each record. The number will start at 1 and increment for every record in order of AnimalName. You can see that that the order of the row number increment is specified as a parameter in to the ROW_NUMBER() function.

SELECT	AnimalID,
	AnimalName,
	AnimalType,
	ROW_NUMBER() OVER(ORDER BY AnimalName) AS RowNumber
FROM	Animal

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,RowNumber
166,Alpaca,Mammal,1
168,Camel,Mammal,2
162,Carabao,Mammal,3
171,Cat,Mammal,4
163,Cattle,Mammal,5
184,Chicken,Bird,6
182,Deer,Mammal,7
185,Duck,Bird,8
186,Goose,Bird,9
189,Pigeon,Bird,10
188,Quail,Bird,11
187,Turkey,Bird,12
[/table]

 
In the above example, the row number never resets. It started at 1 and kept going through all the records. But what if you wanted to reset the row number back to 1 based on a value changing in your result set. In the following example, every time that the AnimalType changes we will reset the row number back to 1. This way, each AnimalType would have it’s own set of row numbers. We accomplish this by listing the columns that we want to group the row numbers by in the PARTITION BY parameter.

SELECT	AnimalID,
	AnimalName,
	AnimalType,
	ROW_NUMBER() OVER(PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber
FROM	Animal A
ORDER	BY AnimalType, AnimalName

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,RowNumber
184,Chicken,Bird[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
185,Duck,Bird,2
186,Goose,Bird,3
189,Pigeon,Bird,4
188,Quail,Bird,5
187,Turkey,Bird,6
166,Alpaca,Mammal[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
168,Camel,Mammal,2
162,Carabao,Mammal,3
171,Cat,Mammal,4
163,Cattle,Mammal,5
182,Deer,Mammal,6
[/table]

 
 
Good Things To Know
You can specify multiple columns in the PARTITION BY and ORDER BY parameters by separating them with a comma.

You can specify ASC or DESC in the ORDER BY parameter if you like as well.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186734.aspx

SQL Server Zero Pad Left

Facebooktwitterredditpinterestlinkedinmail

In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.

SELECT	RIGHT('0000000000' + @TextToZeroPad, 10) AS ZeroPaddedText

In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.

 
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.

CREATE	FUNCTION PadLeft
		(@TextToPad 	 	VARCHAR(8000),
		 @CharacterToPad	VARCHAR(1),
		 @NumberToPad	 	INT)
RETURNS		VARCHAR(8000)
AS
BEGIN
	DECLARE	@OutputText VARCHAR(8000)
	
	-- Create a string with the maximum number of padded characters followed
	-- by the text that we want to pad
	SET		@OutputText = REPLICATE(@CharacterToPad, @NumberToPad) + @TextToPad
	
	-- Take the right X characters to simulate the padding
	RETURN	RIGHT(@OutputText, @NumberToPad)
END

SQL Server Word Sound Comparison

Facebooktwitterredditpinterestlinkedinmail

As we learned in a previous post, you can use the SOUNDEX function to understand the sound that a word makes. You can then compare it to another word to see if the sound matches. This works okay for exact sound matches. But what if you want check to see if the word sound similar to another word? And how do you find out how similar the words are? SQL Server has you covered. In SQL Server 2005 they released the DIFFERENCE function.

 
The DIFFERENCE Function
When you use the SOUNDEX function in SQL Server, it returns a 4 character code that represents the sound of the word. The DIFFERENCE function compares two of these 4 character codes and returns a value between 0 and 4. The return value indicates how many of these characters in the 4 character code were the same. Here is an example:

[table colwidth=”100|100″ width=”200″ colalign=”left|left”]
Word,SOUNDEX Code
Frog,F620
Blog,B420
[/table]

DIFFERENCE: 2

You can see in the example above that if you compare the SOUNDEX code of the two words, only 2 characters are identical. The ‘F’ is different from the ‘B’. The ‘6’ is different from the ‘4’. But the ‘2’ and the ‘0’ are the same between the two codes. Thus… the difference is 2.

 
Put It All Together
Now that you understand how SOUNDEX and DIFFERENCE work, you can use them to create searches that are based on “sounding like a word”. Here is an example… remember, 0 means the words sound nothing alike and 4 means that the words sound exactly alike. In my example, I am going to match on a likeness of 3… which is sounding pretty similar.

DECLARE	@NameToCheckFor	VARCHAR(50)

SET	@NameToCheckFor = 'donky'

SELECT	AnimalName
FROM	Animal
WHERE	DIFFERENCE(AnimalName, @NameToCheckFor) >= 3

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms188753.aspx

SOUNDEX – Discovering The Sounds Of Words In SQL Server

Facebooktwitterredditpinterestlinkedinmail

Let’s face it, not everyone is a perfect speller all the time. Exact match queries are pretty easy to do in SQL Server. But, what if you wanted to correct their spelling or search based on a misspelled word? How is this possible? In SQL Server 2008+ you have the ability to do this with the SOUNDEX function.

SOUNDEX
The SOUNDEX function will take an input word and convert it in to a 4 character code representing the sound of the word. For example: goat -> G300.

So what does this 4 character code mean? The first character represents the first character in the string, the rest of the characters represent the rest of the characters in the string. Of course SQL Server does some magic to come up with this code. If you are interested more in the makeup of this code, see the MSDN link at the bottom of this post. Essentially, this 4 character code represents the sound of the word.

 
How To Use It
You can use this just like any other function in SQL Server.

SELECT	SOUNDEX('dog') AS SoundOfDog

An easy way to search based on word sound would be something like this:

SELECT	*
FROM	Animal
WHERE	SOUNDEX(AnimalName) = SOUNDEX('dug')

The above query would find words like “dog”, “dig”, “dag”

 
Reference: http://msdn.microsoft.com/en-us/library/ms187384.aspx

SQL Server Trim

Facebooktwitterredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

-- Trims the spaces from the left side of the data
SELECT	LTRIM(@TextToTrim) AS LeftSpacesRemoved

-- Trims the spaces from the right side of the data
SELECT	RTRIM(@TextToTrim) AS RightSpacesRemoved

-- Trims the spaces from both sides of the data
SELECT	LTRIM(RTRIM(@TextToTrim)) AS FullTrim

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

CREATE	FUNCTION TRIM(@TextToTrim VARCHAR(8000))
RETURNS	VARCHAR(8000)
BEGIN

	RETURN	LTRIM(RTRIM(@TextToTrim))

END

You can use this new fancy SQL Server Trim function by calling it like this:

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

SELECT	dbo.TRIM(@TextToTrim) AS FullTrim