I hope you may read my earlier posts for Analytical functions and Conversion functions which are introduced in SQL Server 2011 CTP3. You can also move on What's New of Denali CTP3 here. Here i am going to explain new string functions which are also arrived in this SQL Server 11 CTP3 version also.
1. FORMAT: It returns a formatted values with specified format. It requires three arguments, first is value, second parameter is specified format and last one is culture which is optional. If culture is not provided, it will take 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
Below is the result,
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
I have drafted some of the just learned tips for
FORMAT and
CONCAT on www.beyondrelational.com. You can get more tips about SQL Server
HERE.
Hope this post will help you.