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

Finding Sysadmins on a SQL Instance.

In this blog we shall see, how to find the users who have sysadmin rights on your SQL Server.

It is very important to know who are the users who have sysadmin rights, because sysadmin is the highest level of security permission on a SQL Server. A user having this permission can do everything on the SQL Instance, such as Create / Delete / Disable other users including other sysadmins. Create / Drop databases, Start / Stop SQL Server and the list can go on..

Using a T-SQL

We can query the catalog view syslogins and find those rows which have a value of 1 in sysadmin column. The query used below filters to find those users who have been granted access to the SQL Server.  

WHERE sysadmin = 1 and hasaccess = 1

The result when run on one of my personal SQL Server looks as shown below..

I selected an additional column isntname, in order to see what type of login is that user. When isntname = 0, it indicates the login is a SQL login and 1 indicates a windows based login.

Using SSMS

You can also find out using Management Studio. When connected to the SQL Server,  expand Security node, and expand Server Roles. From the list of Server Roles right click sysadmin and choose Properties from the popup Menu item.

You will see the properties dialog box for sysadmin role as shown below..

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

CONCAT – New String Function in SQL Server 2012

CONCAT is a new String function introduced in SQL Server 2012. This function returns an output which is a concatenated string value of the argument values passed in the function. The function would need a minimum of 2 values to be passed.

Let’s take a look at the function. In order to use this function, we shall first create a table and insert few records. The following script is used to the task of creating a table and inserting the data into that table.

CREATE TABLE [dbo].[Customer_Address](
 [CustomerID] [bigint] NULL,
 [Address1] [varchar](50) NULL,
 [Address2] [varchar](50) NULL,
 [ZipCode1] [char](5) NULL,
 [ZipCode2] [char](4) NULL

INSERT INTO Customer_Address
(1,'#1','Moon Walk Drive', 24578,2881 ),
(2,'#2','Roof Top Lane', 54856,5421 ),
(3,'#3','Full Thottle Blvd', 90425,5782 ),
(4,'#4','Drive Slow Road', 18854,6502 )

First let us look at the data in the table by doing a Select * on the table. The pic below displays the result set.

Now let us run the following query which uses the CONCAT function to concatenate the Address1 ad Address2 columns as a single column and also concatenate the 5 character length ZipCode1 and 4 character length Zipcode2 and display a single column output. Note that I am going to use a space ‘ ‘, and a hyphen ‘-‘ in order to display the concatenated column in a meaningful way.

SELECT CustomerID, CONCAT(Address1,' ',Address2) AS Address,
CONCAT(ZipCode1,'-',ZipCode2) AS ZIP
FROM Customer_Address

The below picture shows the result of the query we have just executed.

In previous Versions of SQL Server, you could concatenate string values using a +. If order to achieve the same result you can write the following code.

SELECT CustomerID, Address1 + ' ' + Address2 AS Address,
ZipCode1 + '-' + ZipCode2 AS ZIP
FROM Customer_Address

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