Space function in SQL Server

Few weeks back, I wrote a blog on CONCAT string function in SQL Server 2012. A user asked if the CONCAT function is similar to Space function in SQL Server and what is the difference between the two functions. I replied to him and clarified the difference of the 2 functions. After that I thought, it would be good to write a blog post on Space function too.

Space function

Space function is useful to return ‘x’ number of spaces, where ‘x’ is an integer.  The syntax for the space function is space(x) where x is an integer. To understand the function lets us look a simple script with and without space function.

Without Space function

The below screen shot displays an output where 2 string values are concatenated without using space function and the resulting string is an output that may not be acceptable. See that the resulting string is a single string without any break between first and last name.

Using Space function

The below screen shot displays an output where 2 string values are concatenated and separated by space function. Using space(1) generated a single space (1 space) and when that is used in the string concatenation, output string is now better and acceptable. 

Do you like this site? Like our FB page @\LearnSQLWithBru to know when there is new content.

— Bru Medishetty

How to Install Sample Databases in SQL Server 2008 R2

In this blog, we shall learn “How to install sample databases in SQL Server 2008 R2”. Sample databases are really helpful,  when you want to practice what you have learnt in SQL Server be it from a text book or an online article, blog etc sample databases are readily available to test. Knowing that it is easy to start from scratch on these sample databases makes you play with and even try some things that you would not want to do on other databases. Database and Log file shrinking (which is not suggested on user databases) but in order to learn such topics, your best choice is a sample database. With that lets focus on our goal (Installing the sample databases, if you have already forgotten…)

The first step is downloading the sample database. Visit the Microsoft Database Product Samples website and choose the sample database for the version that you want. Since we are dealing with SQL Server 2008 R2 he is the link. Once you have downloaded the file (approx 80 MB). Go to the location where the downloaded file is residing. Right click on the file AdventureWorks2008R2_SR1.exe (if you have not renamed it) and select Open; you can also double click on the file.

The exe file starts extracting to a temporary location on your local drive and once the extraction is done, the SQL Server 2008R2 SR1 Database Installer is displayed. The below screen is the first screen in which you have to accept to the license terms. Choose the check box and click Next.

In the next screen, it will displays on which database Instance the Sample databases will be added. If you have multiple instances on the machine, you can choose the Instance name from the drop down list.

Note that for some databases there are warning (yellow triangle with exclamation) icon being displayed, those databases cannot be installed through this installed but they can be added manually using Business Intelligence Developer Studio. You can click more information to find details on how to perform that task.

By default all sample databases that can be added, will be selected, you can un-check those databases that you would not want to install. Once you are done, click Install

The installation of sample databases progress is displayed, you can click Show Details button to view the detailed information in text format..

Once the installation is done, the progress bar indicates that installation is complete.. Click Finish to close the Installer.

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

— Bru Medishetty

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 @\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty