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.
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.
Text to search
Text to find
Text to replace with
SELECTREPLACE('Full text to search in','search','replace')ASReplacedText
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.
SELECTREPLACE('Full text to search in'COLLATESQL_Latin1_General_CP1_CS_AS,'SEARCH','replace')ASReplacedText
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”.
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.
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.
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.
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.
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.