Category Archives: SQL Server Development

CONCAT – New String Function in SQL Server 2012

Posted on by
2

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
VALUES
(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

Using WHERE Clause in SQL Server Query

Posted on by
Reply

In this blog we shall look into the usage of WHERE clause in SQL Server. WHERE clause can be used in Select, Update, Delete statements to filter the rows being affected by the query. In fact it can be used inside an Insert statement too which has a Select statement. WHERE clause ensures the data is filtered when it is retrieved in select statements or it ensures only those records are affected when updating / deleting is performed.

A WHERE clause contains one or more conditions depending on which the records are filtered in the table(s). The conditions in WHERE clause can be based on one or more operators and the operators that can be used in a WHERE clause can be =, <>, != , >, <, BETWEEN, IN, LIKE, NOT and many more. When there are multiple conditions, those conditions can be combined with AND / OR.

Let us look at some examples to understand different types of WHERE clause.

Examples for WHERE Clause

Example 1: Using “=”

SELECT EmployeeID, Title, Gender, HireDate
 FROM AdventureWorks.HumanResources.Employee
 WHERE Title = 'Production Technician - WC20'

 In the above SQL statement, the condition used in WHERE ensure that those records that have the value “Production Technician – WC20” in the Title column will be displayed. You might have noticed that the string value Production Technician – WC20 is enclosed in single quotes, the reason being that all string values and Date and time related columns need to compared with at value which is enclosed in single quotes.

Example 2 A: Using “LIKE” and %

SELECT EmployeeID, Title, Gender, HireDate
FROM AdventureWorks.HumanResources.Employee
WHERE Title LIKE '%Technician'

In the above SQL statement, the condition used in WHERE is more wider than the one in previous example. It uses LIKE keyword to filter those records that end with the value “Technician” in the column Title. Notice that when we use LIKE we do not use = symbol as we would like to filter the data according to a patterm matching and the criteria in this condition is any rows / records which end with the string Technician. You also notice that ‘%’ is used before the string, which indicates that any string value in that position is valid in the condition.

SELECT EmployeeID, Title, Gender, HireDate
FROM AdventureWorks.HumanResources.Employee
WHERE Title LIKE '%Technician%'

Example 2 B: Using “LIKE” and %

In this statement, the condition is little bit changed to that in previous example. It has an additional ‘%’ at the end of the expression, indicating that any record is a valid record which contains the string “Technician” in column Title, no matter if it is at the beginning or startig or the end of the string value.

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

Dropping a Column from a table using T-SQL – SQL Server

Posted on by
Reply

This blog explains how to drop a column from a table using T-SQL query. It might be easy to do it from SSMS, on the other side, it is not always convenient to do it from SSMS. When you have to repeat the task of dropping additional columns at a later time, you will have to go back to the object explorer repeatedly and choose those column to be dropped, where as using T-SQL query you just need to change the column name to be dropped. Now let’s look at the script to do it.

ALTER TABLE EmployeeTable DROP COLUMN Gender 
Go

The Syntax to drop a table column is quite simple, it goes this way “ALTER TABLE YOURTABLENAME DROP COLUMN YOURCOLUMN“.

To use the code in this example, first you will need to create the table EmployeeTable using the script in another blog Creating Primary Key in a SQL Server table

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