ROW_NUMBER – How To Use It

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

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

 
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.

AnimalID AnimalName AnimalType RowNumber
184 Chicken Bird 1
185 Duck Bird 2
186 Goose Bird 3
189 Pigeon Bird 4
188 Quail Bird 5
187 Turkey Bird 6
166 Alpaca Mammal 1
168 Camel Mammal 2
162 Carabao Mammal 3
171 Cat Mammal 4
163 Cattle Mammal 5
182 Deer Mammal 6

 
 
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

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

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.

SQL Server Word Sound Comparison

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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:

Word SOUNDEX Code
Frog F620
Blog B420

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.

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

SOUNDEX – Discovering The Sounds Of Words In SQL Server

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

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

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

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

SQL Server Trim

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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

 
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.

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