SQL Server string function REPLACE with Examples

In this blog we shall learn a string function called REPLACE. We shall use REPLACE function in Select as well as Update statements with examples to understand it better.

We shall start with a basic Select statement using Replace function to understand what it does. 

In simple terms, a REPLACE function takes in a string as first parameter and then accepts a pattern as 2nd parameter, searches for that pattern in the first parameter and replaces with what is passed in the 3rd parameter.

Let us look at an example to understand better. See the below select statement with Replace function. The string value ‘Easy very easy’ is passed as first parameter, then the second parameter ‘EASY’ is passed which is the string pattern, so the Replace functions’ main objective is to find that pattern in the string passed and replace with the value ‘tough’ where ever it sees the string pattern ‘EASY’. Hence it returns ‘tough very tough’ as output. Since there are 2 occurrences of easy in the string.. (see 1st line of the output)

SELECT REPLACE('Easy very easy','EASY','tough')

Note: I ran all queries at once and captured the output, in order to post the results as a single screenshot.

In the next example, we shall see what will happen if we pass an integer values as the 2nd and (or) 3rd parameters. The REPLACE function will still return an output by converting the integer values to character or string values.  (see 2nd line of the output)

SELECT REPLACE('Let us say 123',123,777)

See that 123 was not surrounded by single quotes as ‘123’, but still the function converts it to string value 123. Note, this does not mean that you pass the values without single quotes.

Next, we shall see what happens if one of the parameters is a NULL. If one of the parameters is a NULL value, the output returned will be a NULL. (see 3rd line of the output) 

SELECT REPLACE('In case of nulls',NULL,'Nothing')

Finally, lets look at what happens when you pass a single space as search pattern. 

SELECT REPLACE('Not NULL but Space',' ','BlankSpace')

Since the string value ‘Not NULL but Space’ has 4 single spaces, the output will be enerated by replacing those 4 single spaces with the string value ‘BlankSpace’. (see 4thd line of the output)

Next, let us look at how to use REPLACE function when performing an update to a table data.  Before we update table data, first let us run this query to see how the data looks like.

SELECT TOP 5 EmployeeID, LoginID
FROM HumanResources.Employee

The below is the screenshot when the above query is run..

Now, I would like to replace the value “adventure-works” with “LearnSQLWthBru” in all the rows of the table. So we use Replace function in the “set columnname = expression” in the Update statement, as shown below….

UPDATE AdventureWorks.HumanResources.Employee
SET LoginID = REPLACE(LoginID,'adventure-works','LearnSQLWithBru')

After running this query, re-run the initial select query against the table to see how the data looks after the update.. The pic displayed below is how it looks..

Suggestion: When performing an update against a table, it is a better to run a select statement and include the where clause (to be used in Update statement) to make sure you are going to update as many records as it returns in the select statement.. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

What are SQL Server Developer Responsibilities?

Couple of weeks back I published What are SQL Server DBA Responsibilities? After that blog, while I was writing notes for the section Learn SQL Server, I thought, why not post a blog that lists out the responsibilities of a SQL Server Developer too.This would also complete the topic of what responsibilities of these 2 positions (DBA and Developer) under my new section Learn SQL Server.

  • Create Entity Relationship (ER) Diagrams to the proposed database
  • Create database objects such as tables, views, stored procedures, Triggers etc.
  • Maintain referential integrity, domain integrity and column integrity by using the available options such as constraints etc.
  • Identify columns for Primary Keys in all the tables at the design time and create them.
  • Create functions to provide custom functionality as per the requirements.
  • Be aware of potential blocking, deadlocking and write code to avoid those situations.
  • Endure that the code is written keeping in mind any security issues such as SQL Injection.
  • Develop reports in SQL Server Reporting Services.
  • Design, Develop and Deploy SSIS Packages.
  • Identify and write best possible code in case of new deployments or when rewriting code when migrating to newer version of SQL Server.
  • participate in discussions involving the application creation and understand the requirements and provide the back-end functionality for the applications.
  • Participate in development and creation of Data warehouses.
  • Create cubes in SQL Server Analysis Services.

Since I primarily work as a SQL Server Database Admin, I might not be able to give an exhaustive list covering all responsibilities of a SQL Server Developer. If you feel something is missing, please feel free to post a comment.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

OFFSET and FETCH – New in SQL Server 2012

In this article we shall learn how to limit the numbers of rows returned by a query using OFFSET and FETCH clause introduced in SQL Server 2012. The following query is executed against AdventureWorks Database.

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID

The following is the query result set when the above T-SQL code is executed.

Observe that the data is displayed sorted by the column SalesOrderDetailID, which is very much clear from the ORDER BY clause at the end of the query. This query will display all records in that table as we have not included any filtering condition.

You might be aware that using TOP clause we can restrict the query result set to as many rows we want it to display. Instead of the top n records, if we wanted to return x number of records from the middle of the result set, sorted on a particular column, we would have to do some additional manipulations and retrieve them. 

In SQL Server 2012, this can be achieved by using the OFFSET and FETCH clause at the end of the Select query, after ORDER BY clause. Let’s take a look at this sample query to understand this better..

 SELECT SalesOrderID,SalesOrderDetailID,OrderQty,SpecialOfferID
 FROM [Sales].[SalesOrderDetail]
 ORDER BY SalesOrderDetailID 

The OFFSET clause sets how many rows needs to be skipped before displaying the result set. In this case we gave OFFSET 5 ROW, so the first 5 rows are skipped. FETCH NEXT x ROW ONLY, displays the next x records, if there are records inside the table. In our example script, we used 10 so 10 rows are displayed. If we mention 10000000 rows, and if there are only 1000 rows in the table, then only 995 rows will be displayed. A small clarification, the keywords ROW or ROWS both will work the same.

Note: Please note that this is correct as of SQL Server 2012 RC0.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty