Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

New FORMAT and CONCAT Functions in SQL Server 2012


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Functions - System

Problem

SQL Server 2012, has new String functions such as FORMAT and CONCAT that can be used to perform an operation on a string input value and return a string or numeric value. In this tip we take a look at some basic examples of how this could be used.

Solution

In this tip we will discuss how to utilize the below mentioned new String functions available in SQL Server 2012.

1. FORMAT Function
2. CONCAT Function

Using FORMAT Function

The FORMAT function, which is available in SQL Server 2012, returns a returns a value formatted with the specified format and optional culture as shown in the below query result.

--SYNTAX: - FORMAT (value, format [, culture])

/* Using Format Function */
DECLARE @TodaysDate DATETIME = GETDATE() 
SELECT FORMAT ( @TodaysDate, 'd', 'en-US' ) AS [Using 'en-US' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'de-DE' ) AS [Using 'de-DE' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'en-IN' ) AS [Using 'en-IN' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'en-CA' ) AS [Using 'en-CA' Culture]
GO
DECLARE @ProductCost INT = 100
SELECT FORMAT ( @ProductCost, 'c', 'en-US' ) AS [Using 'en-US' Culture]
SELECT FORMAT ( @ProductCost, 'c', 'de-DE' ) AS [Using 'de-DE' Culture]
SELECT FORMAT ( @ProductCost, 'c', 'en-IN' ) AS [Using 'en-IN' Culture]
GO

sql server format function examples

Using CONCAT Function

The CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values as shown in the below query result. The CONCAT function implicitly coverts all arguments to string types and then concatenate the inputs. The CONCAT function requires a minimum of two input values else the concatenation fails.

--SYNTAX: - CONCAT (string_value1, string_value2 [,string_valueN])
/* Using CONCAT Function */
Use AdventureWorks2008R2
GO
SELECT
 TOP 10 
 CONCAT( PP.FirstName ,' ',
  PP.LastName ,',',
  PA.AddressLine1 ,',',
  PA.AddressLine2 ,'',
  PA.City ,',',
  PA.PostalCode) 
FROM Person.Address PA
 INNER JOIN Person.BusinessEntityAddress PBEA 
  ON PA.AddressID = PBEA.AddressID 
 INNER JOIN Person.Person PP 
  ON PBEA.BusinessEntityID = PP.BusinessEntityID
GO

sql server concat function examples

Developers can also use the CONCAT function with a PRINT statement an example is shown below.

SET NOCOUNT ON
SELECT CONCAT('MSSQLTips published this tip on ', GETDATE())
GO
PRINT CONCAT('MSSQLTips published this tip on ', GETDATE())
GO
SET NOCOUNT OFF
Results
---------------------------------------------------
MSSQLTips published this tip on Jan 21 2011 12:00AM
MSSQLTips published this tip on Jan 21 2011 12:00AM
Next Steps


Last Update:






About the author


 



More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, January 21, 2016 - 10:32:03 AM - Thomas Franz Back To Top

Please be aware, that FORMAT() is MUCH (about 17 times) slower than a CONVERT(varchar, GetDate, 112) -- replace 112 with the style you want.

On the other hand it provides more comfort / flexiblity than CONVERT.

So it is ok, if you use it only for a few records, but be aware in your big reports mit millions of rows.


Thursday, July 23, 2015 - 1:07:36 AM - Debpratim Back To Top

how can i join two attributes from two diffrent tables without using concat function and diplay it in a single field?? Is this possible? if it is then please let me know.


Friday, December 27, 2013 - 12:10:45 PM - ados Back To Top

Nice thing is that this function will accept a null and use it as a zero length string rather than nulling the whole result.


Learn more about SQL Server tools