You are here:Home » tsql » New String functions arrived by SQL Server Denali CTP3

New String functions arrived by SQL Server Denali CTP3

I hope you may read my earlier posts for the Analytical functions and Conversion functions which are introduced in SQL Server Denali. You can also move on the What's New of Denali CTP3 here. Here I am going to explain new string functions which are also arrived in this SQL Server Denali version also.

1. FORMAT :
It returns a formatted value with specified format. It requires three arguments, first is value, second parameter is specified format and the last one is a culture which is optional. If culture is not provided, it will take a default culture. Let us run below queries to get formatted datetime values. It returns resulted output in nvarchar datatype or null. Let us run some below queries to know it better. First, we will run it format for datetime.
DECLARE @DATEVAR DATETIME
SET @DATEVAR =  GETDATE()

SELECT FORMAT( @DATEVAR, 'dd-MM-yyyy') -- 18-07-2011
UNION
SELECT FORMAT( @DATEVAR, 'yyyy-MM-dd', 'en-US' ) -- 2011-07-18
UNION
SELECT FORMAT( @DATEVAR, 'MM-dd-yyyy', 'en-US' ) -- 07-18-2011
UNION
SELECT FORMAT( @DATEVAR, 'MM-dd-yy', 'en-US' ) -- 07-18-11
UNION
SELECT FORMAT( @DATEVAR, 'MM/dd/yy', 'en-US' ) -- 07/18/11
UNION
SELECT FORMAT( @DATEVAR, 'MMddyyyy', 'en-US' ) -- 07182011
UNION
SELECT FORMAT( @DATEVAR, 'yyyy', 'en-US' ) -- 2011
UNION
SELECT FORMAT( @DATEVAR, 'dd', 'en-US' ) -- 18
UNION
SELECT FORMAT( @DATEVAR, 'MM', 'en-US' ) -- 07
UNION
SELECT FORMAT( @DATEVAR, 'hh', 'en-US' ) -- 01
UNION
SELECT FORMAT( @DATEVAR, 'hh:mm', 'en-US' ) -- 01:33
UNION
SELECT FORMAT( @DATEVAR, 'hh:mm:ss', 'en-US' ) -- 01:33:59
UNION
SELECT FORMAT( @DATEVAR, 'MM-dd-yy hh', 'en-US' ) -- 07-18-11 01
UNION
SELECT FORMAT( @DATEVAR, 'MM-dd-yy hh:mm', 'en-US' ) -- 07-18-11 01:33
UNION
SELECT FORMAT( @DATEVAR, 'MM-dd-yy hh:mm:ss', 'en-US' ) -- 07-18-11 01:33:59

GO

Now will run format function for currency. You can get all currency symbol from here
DECLARE @money MONEY
SET @money = 50.50

SELECT 'English - United States',FORMAT( @money, 'c', 'en-US' ) 
UNION
SELECT 'Japanese', FORMAT( @money, 'c', 'ja' ) 
UNION
SELECT 'Arabic - Iraq', FORMAT( @money, 'c', 'ar-IQ' ) 
UNION
SELECT 'Icelandic', FORMAT( @money, 'c', 'is' ) 
UNION
SELECT 'Indonesian', FORMAT( @money, 'c', 'id' ) 
UNION
SELECT 'Italian', FORMAT( @money, 'c', 'it' ) 
UNION
SELECT 'Kannada', FORMAT( @money, 'c', 'kn' ) 
UNION
SELECT 'Kazakh', FORMAT( @money, 'c', 'kk' ) 
UNION
SELECT 'Malay', FORMAT( @money, 'c', 'ms' ) 
UNION
SELECT 'Portuguese', FORMAT( @money, 'c', 'pt' ) 
UNION
SELECT 'Russian - Russia', FORMAT( @money, 'c', 'ru-RU' ) 
UNION
SELECT 'Spanish', FORMAT( @money, 'c', 'es' ) 
UNION
SELECT 'Thai', FORMAT( @money, 'c', 'th' ) 

GO

2. CONCAT :
CONCAT (tsql function) which is used to concatenate two or more strings. This function requires 2 to 254 arguments. Let us run below queries to get output with using this function.
SELECT CONCAT('FirstName',' ','LastName' ) 
UNION
SELECT CONCAT('Paresh',' ','Prajapati' )  
UNION
SELECT SUBSTRING(CONCAT('My birthday',':','20111101' ),1,11) 

GO

We can also use CONCAT as computed column.
IF(object_id('EmpDetails','U') > 0)
DROP TABLE EmpDetails

CREATE TABLE EmpDetails
(
EmpId int,
EmpFirstName varchar(50),
EmpLastName varchar(50),
EmpFullname as CONCAT(EmpFirstName,' ',EmpLastName) ,
EmpAddress1 varchar(100),
EmpAddress2 varchar(100),
EmpCity varchar(20),
EmpState varchar(20),
EmpCountry varchar(20),
EmpFullAddress as CONCAT(EmpAddress1,', ',EmpAddress2,', ',EmpCity,', ',EmpState,', ',EmpCountry)
)

INSERT INTO EmpDetails
(
EmpId,
EmpFirstName,
EmpLastName,
EmpAddress1,
EmpAddress2,
EmpCity,
EmpState,
EmpCountry
)
SELECT 
1,
'Paresh',
'Prajapati',
'37- Abhayratna row house',
'NR- Chenpur railway crossing',
'Ahmedabad',
'Gujarat',
'India'

SELECT 
EmpFullname,
EmpFullAddress
FROM EmpDetails

GO

Hope this post will help all readers.