Space function in SQL Server

Posted on by
1

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 @ Facebook.com\LearnSQLWithBru to know when there is new content.

— Bru Medishetty

Using SELECT INTO to Create a Test Table

Posted on by
4

While working with SQL Server tables, especially when you are testing a certain code, you would want to create a test table that is similar to the actual / original table. That way you don’t have to worry about accidental updates or data loss etc.

During such situations, creating an an exact replica of that table can be achIeved using SELECT INTO statement. Below is a script to create an exact replica of Person.Address table found in AdventureWorks Sample Database.

SELECT * INTO AdventureWorks.dbo.Person_Address_Test
FROM AdventureWorks.Person.Address

Below is a screen shot of the query run on AdventureWorks DB on a SQL Server 2008 R2 Instance.

What this SELECT INTO does is, it creates a new table (if it does not exist) and inserts the data into that table. Since we have not filtered any rows with a WHERE clause, all the data from the table Person.Address is now readily available in the Test table. If there is a table that already exists with that chosen name, the statement fails, indicating there is already a table with that name.

Now you run any command on the newly created table, the below is a simple select statement with top 10 *.

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

— Bru Medishetty

SQL Server Feedback and request new features / additions

Posted on by
Reply

Have you even wondered how you can give feedback on SQL Server team @ Microsoft? Follow this link below to visit the Feedback Center for SQL Server…. https://connect.microsoft.com/SQLServer/Feedback

On this page, you can sign-in and leave feedback about a specific bug you have come across or make a request for a new feature that you would like to see in SQL Server. There are a number of bugs reported by other users and for some bugs, workarounds are also would be available. Also available is a huge list of suggestions or requests, that you can look into and vote for it, if you like the suggestion…

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