functions

Get Filestream Storage Directory

by
Facebooktwitterredditpinterestlinkedinmail

Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system somewhere. But what if you want to get access to these files or if you just want to know where they’re stored. SQL Server provides a function called PathName that you can call to get this path. You call this function like you would call an extension method in .Net. In the example below, we’ll assume that the column InvoiceFile is a Filestream datatype.

SELECT	TOP 1
		InvoiceFile.PathName() AS DirectoryOfFile
FROM	Invoice

SQL Server Replace

by
Facebooktwitterredditpinterestlinkedinmail

Replacing text in SQL Server is easy. SQL Server has the REPLACE function to perform this task.

SQL Server REPLACE
The REPLACE function in SQL Server has 3 parameters.

  1. Text to search
  2. Text to find
  3. Text to replace with
SELECT	REPLACE('Full text to search in', 'search', 'replace') AS ReplacedText

The output is: Full text to replace in

In the above example, we are searching “Text to search in” for the word “search” and we are replacing it with the word “replace”. Pretty simple, right? Now let’s take it a step further.

SQL Server Case Sensitive Replace
Above we went over how to do a case insensitive replace on a string. (The REPLACE function actually uses the default collation of the input text that it is searching). To turn it in to a SQL Server case sensitive replace, we just need to add one small thing to the end. We need to change the collation of the text we are searching. Learn more about text collation here.

SELECT REPLACE('Full text to search in' COLLATE SQL_Latin1_General_CP1_CS_AS, 'SEARCH', 'replace') AS ReplacedText

The output is: Full text to search in

This example shows how the “SEARCH” text was not found because we are changing the input text to search collation to SQL_Latin1_General_CP1_CS_AS (which is case sensitive). The input string has “search” in lower case and we were searching for an upper case “SEARCH”.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186862(v=sql.120).aspx

SQL Rank Vs Row Number

by
Facebooktwitterredditpinterestlinkedinmail

SQL Rank Vs Row Number is a common question because the two functions are very similar. SQL Server has the two similar built-in functions to assign numbers to the order of the data in the result set. The SQL Server RANK function and the ROW_NUMBER function both can accomplish this, but they do something slightly different. You can follow these links see details on how to use this functions at RANK function and ROW_NUMBER function.

ROW_NUMBER: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will continue the sequence on the duplicated record… ensuring that the row number sequence is never duplicated.

RANK: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will use the same number for the duplicate records. There will then be a gap in the sequence for the duplicated records.

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

ROW_NUMBER – How To Use It

by
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

by
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