jump to navigation

Looking into Concat string function in SQL Server 2012 July 16, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags:
trackback

I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.

In this rather sort post I will demonstrate with  hands-on examples how to use the new string function,CONCAT().

You can have a look in another blog post of mine related to built-in SQL functions.You can have a look into the new logical functions introduced in SQL Server 2012, here .

You can have a look in another blog post of mine explaining the new conversion functions in SQL Server 2012.

You can use any edition of SQL Server of 2012 you want. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these examples.

This function (CONCAT()) allows multiple string values to be joined (concatenated) together.This function implicitly converts the data values to a string and NULL values are converted to an empty string.

Type and execute the code below


SELECT CONCAT ('Liverpool', ' ' , 'is', ' ', 'the', ' ', 'best', ' football', ' ', 'team', ' ' ,'in', ' ' ,'the', ' ' ,'world')

SELECT Concat('Nikos',Null,' Kantzelis', ' Mr.')

SELECT CONCAT ('Usain', ' ' , 'Bolt', ' ' , 'came', ' ', 1, 'st', ' ' ,'in', ' ' , 'the', ' ' , 'Olympics')

All these strings in the first statement are now concatenated into one.

The same happens for the second statement but please note that the NULL value is converted to an empty string.

In the third statement the strings are concatenated.Please note  that CONCAT() function in this case will convert the value of 1 to its string equivalent.

Let’s try this new function in a table

Type and execute the code below


USE tempdb;
GO

CREATE TABLE [dbo].[Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](30) NOT NULL,
[Country] [nvarchar] (30) NOT NULL,
[City] [nvarchar] (30) NOT NULL,
[PostalCode] [nvarchar] (30) NOT NULL,
[HireDate] [date] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

INSERT INTO Employee (FirstName, LastName,Country,City,PostalCode,HireDate)
VALUES
('Robbie', 'Fowler','USA', 'New York', '434343','1/10/2004'),
('Steven', 'Gerrard','Ireland','Dublin','75676','10/3/2000'),
('Michael', 'Owen', 'Britain','London','87677','2/4/2002')

SELECT CONCAT(FirstName, ' ' ,LastName) AS FULLNAME FROM Employee

I create a table and I insert some values in it. Then in the last statement I concatenate the first and the last name for all the rows in the table.

Hope it helps!!!

Comments»

1. Dot Net Rules : Looking into Concat string function in SQL Server 2012 - July 16, 2012

[…] post I will demonstrate with  hands-on examples how to use the new string function,CONCAT(). (read more) Share Posted: Δευτέρα, 16 Ιουλίου 2012 3:03 πμ από το μέλος […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: