Feeds:
Posts
Comments

Archive for the ‘String Function’ Category

Format function is one the important functions in SQL Server 2012. This function is very useful to format data in a specified format.

Today, we will discuss each and every aspect of this function. Also we will discuss the need/importance of this function in SQL server.

By using this function, we do not need to do a lot of scripting to format the data.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

      FORMAT ( value, format [, culture ] )

Parameters :

@value : Data to be converted in other format. (Mandatory)
@format : To define a format in nvarchar datatype. (Mandatory)
@culture : To define a culture. By default, it picks up culture from session. (Optional)

Purpose :
This function converts value from one format to another format by using culture. If culture is not available it will pick up the culture from the session. It will return value either in nvarchar data type or NULL.

Let me explain this with simple examples.

Example-1 : FORMAT – DATE WITH DEFAULT CULTURE

DECLARE @Date AS datetime=Getdate()
 
Select @Date AS [DATE]
SELECT FORMAT (@Date, 'dd/MM/yyyy') as [Result (dd/MM/yyyy)]
SELECT FORMAT (@Date, 'MM/dd/yyyy') as [Result (MM/dd/yyyy)]
SELECT FORMAT (@Date, 'yyyy/MM/dd') as [Result (yyyy/MM/dd)]

Example-2 : FORMAT – TIME WITH DEFAULT CULTURE

DECLARE @TIME AS DATETIME=Getdate()
 
Select @TIME AS [TIME]
SELECT FORMAT (@TIME, 'h:mm:ss tt') as [Result (h:mm:ss tt)]
SELECT FORMAT (@TIME, 'hh:mm:ss tt') as [Result (hh:mm:ss tt)]
SELECT FORMAT (@TIME, 'h:mm:ss') as [Result (h:mm:ss)]
SELECT FORMAT (@TIME, 'hh:mm:ss') as [Result (hh:mm:ss)]

Example-3 : FORMAT – NUMBER WITH DEFAULT CULTURE

DECLARE @NUMBER AS NUMERIC(18,4)=12345.789

Select @NUMBER as [NUMBER]
SELECT FORMAT (@NUMBER, '####0.00') as [Result (####0.00)]
SELECT FORMAT (@NUMBER, '####0.000') as [Result (####0.000)]
SELECT FORMAT (@NUMBER, '##,##0.00') as [Result (##,##0.00)]
SELECT FORMAT (@NUMBER, '#') as [Result (#)]

Example-4 : FORMAT – CURRENCY WITH DEFAULT CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C') AS  [RESULT]

Example-5 : FORMAT – DATE & TIME WITH MULTIPLE CULTURE

DECLARE @Date AS datetime=Getdate()

Select @Date as [DATE]
SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') 
AS [English Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','de-DE') 
AS [German Culture]	

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ja-JP') 
AS [Japanese Culture] 

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ar-SA') 
AS [Arabic Culture]

SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ur-PK') 
AS [Urdu Culture]

Example-6 : FORMAT – CURRENCY WITH MULTIPLE CULTURE

DECLARE @CURRENCY MONEY = '24500.50';
SELECT FORMAT (@CURRENCY, 'C','en-US' ) AS [English Culture]	
SELECT FORMAT (@CURRENCY, 'C','de-DE' ) AS [German Culture]	
SELECT FORMAT (@CURRENCY, 'C','ja-JP' ) AS [Japanese Culture] 
SELECT FORMAT (@CURRENCY, 'C','ar-SA' ) AS [Arabic Culture]
SELECT FORMAT (@CURRENCY, 'C', 'ur-PK' ) AS [Urdu Culture]

Example-7 : FORMAT – VALUE BY USING LANGUAGE INSTEAD OF CULTURE

SET LANGUAGE 'ENGLISH'

SELECT FORMAT (245000, 'C') AS [Currency]
SELECT FORMAT (GETDATE(),'D') AS [DATE]
SELECT FORMAT (24500.282, '#,###,###0.00') AS [Currency]

Reference : MSDN

Advertisements

Read Full Post »

SQL Server 2012 has introduced a new string function namely “CONCAT”. It is a very handy & important function to deal with the string concatenation.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

     CONCAT ( string_value1, string_value2 [, string_valueN ] )

Parameters :
@string_value1 : First string value to concatenate with other strings.
@string_value2 : Second string value to concatenate with other strings.
It will go up to (n) Numbers of strings.

Purpose :
It concatenates two or more than two strings and returns as a single string value. Minimum we need to pass two strings to concatenate otherwise it will generate an error. It implicitly converts parameters to string. If The parameters are NULL it treats it as empty string of varchar(1).

Let me explain this with simple examples.

Example-1 : CONCAT – String values

Select Concat('SQL Server ','2012') as [Result]

Result
---------------
SQL Server 2012

(1 row(s) affected)

Example-2 : CONCAT – Integer values
In this example, “CONCAT” function will implicitly convert integer data type to string data type and concatenate it.

Select CONCAT(1,2) as [Result]

Result
------------------------
12

(1 row(s) affected)

Example-3 : CONCAT – Numeric values
In this example, “CONCAT” function will implicitly convert numeric data type to string data type and concatenate it.

Select CONCAT(12.34,56.78) as [Result]

Result
----------------------------------------------------------------------------------
12.3456.78

(1 row(s) affected)

Example-4 : CONCAT – Less than 2 strings

Select Concat('SQL Server ') as [Result]

Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Example-5 : CONCAT – Passed NULL as parameter
It converts NULL to empty string of varchar(1) and return it.

Select Concat('SQL Server ',NULL) as [Result]

Result
---------------
SQL Server 

(1 row(s) affected)

Example-6 : CONCAT – Passed NULL Values from Database
Before in SQL server 2012, we had to use ISNULL to convert NULL to empty string & concatenate.
But this function resolves this issue. It automatically converts NULL to empty string. Lets use Adventure database to test it.

Select top 10 [FirstName] ,  [MiddleName] , [LastName] 
,[FirstName] +  [MiddleName] + [LastName] as [Full Name]
from [Person].[Person]

In the above example, due to the NULL values in the middle name column SQL failed to concatenate it and return NULL as [Full Name]

Lets try to do the same with “CONCAT” function.

Select top 10 [FirstName] ,  [MiddleName] , [LastName] 
,Concat([FirstName] , [MiddleName] , [LastName]) as [Full Name]
from [Person].[Person]

In the above example, CONCAT function replaced NULL with emprty string and concatenates the FULL Name successfully.

Reference : MSDN

Read Full Post »