New
T-SQL features in SQL server 2012
String
functions
String functions perform an operation on a string value
that you input, returning another string (or sometimes numeric) value. New to
2012 are the CONCAT() and FORMAT() functions.
CONCAT
It will concatenate, or join them together and return an
output string.
Basic structure
CONCAT (
string_value_1, string_value_1 [, string_value_n ] )
It is a really useful feature to this function which,
create a temporary table and add a couple of records.
CREATE TABLE #Cust
(
FirstName varchar(30) NOT NULL,
MiddleName varchar(30) NULL,
LastName varchar(30) NOT NULL
)
INSERT INTO #Cust
VALUES ('Larry', 'Jane', 'Colin'), ('Jon', NULL, 'Reade')
Let us use the CONCAT function to return the full name
SELECT CONCAT(FirstName + ' ', MiddleName + ' ',
LastName) AS
CustomerName
FROM #Cust
This returns the full names of our two customers, just as
you would expect. Now, this is where the function really shows its worth. If we
run the following statement:
SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS
CustomerName
FROM #Cust
As you can see, our second customer, who doesn't have a
middle name, is returned as a NULL value. Using the CONCAT function we can
return a concatenation of values, allowing for NULL values.
FORMAT
For locale-related formatting of strings and numbers, the
new FORMAT function comes to the rescue. FORMAT will take any culture supported
by the .NET Framework
FORMAT ( value, format [, culture ] )
The first parameter (value) is the variable to
apply the formatting to. The second argument, format, is the format of the
value. Take care with the format argument, as this must be a supported .NET
Framework format. Culture is the locale you would like to apply to the
value. If you choose not to supply a culture, the function will use that of the
current session.
SELECT FORMAT(GETDATE(), N'"Current Time is
"dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;
FORMAT is not just for dates; it can be used for time,
money, and decimal locale conversions
Date
time functions
EOMONTH
It will returning the last day of the month you specify.
DECLARE @MyDate datetime
SET @MyDate = '09/02/2013'
SELECT EOMONTH (@MyDate) AS LastDayOfTheMonth
As you can see from the following result this returns the
last day of the month of September :
Let us see what the end of month date is six months on
from our test date
DECLARE @MyDate datetime
SET @MyDate = '09/02/2013'
SELECT EOMONTH (@MyDate,6) AS LastDayOfTheMonth
Six months on and the last day of the month is as follows
we can back six months
DECLARE @MyDate datetime
SET @MyDate = '09/02/2013'
SELECT EOMONTH (@MyDate,-6) AS LastDayOfTheMonth
DATEFROMPARTS
If you parameters and returns them as a date variable
(year, month and day). Currently your code for returning these parameters as a
date would probably look something like this:
DECLARE @Year int, @Month int, @Day int
SET @Year = 2013
SET @Month = 09
SET @Day = 02
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '/'
+
CONVERT(varchar(10),@Month) + '/' +
CONVERT(varchar(10),@Day),103) AS MyDate
As you can see from the preceding code, there is a lot of
work converting from integer values to varchar and back to a datetime value. By
using the new DATEFROMPARTS to
remove the conversion code.
DECLARE @Year int, @Month int, @Day int
SET @Year = 2013
SET @Month = 09
SET @Day = 02
SELECT DATEFROMPARTS
(@Year, @Month, @Day) AS MyDate
TIMEFROMPARTS
The TIMEFROMPARTS function works in exactly the same way,
you pass in time parameters instead:
DECLARE @Hour int, @Minutes int, @Seconds int,
@FractionsOfASecond int, @SecondsPrecision int
SET @Hour = 15
SET @Minutes = 23
SET @Seconds = 47
SET @FractionsOfASecond = 500
SET @SecondsPrecision = 3
SELECT TIMEFROMPARTS (@Hour, @Minutes, @Seconds,
@FractionsOfASecond, @SecondsPrecision) AS MyTime
DATETIMEFROMPARTS
You can combine time and date with the DATETIMEFROMPARTS
function and this will return a datetime variable to you.
DECLARE @Year int, @Month int, @Day int, @Hour int
DECLARE @Minutes int, @Seconds int, @MilliSeconds int
SET @Year = 2013
SET @Month = 09
SET @Day = 02
SET @Hour = 17
SET @Minutes = 27
SET @Seconds = 49
SET @MilliSeconds = 0
SELECT DATETIMEFROMPARTS (@Year, @Month, @Day, @Hour,
@Minutes,
@Seconds, @MilliSeconds) AS MyDateTime
DATETIMEOFFSETFROMPARTS
Show the offset of hours and minutes so you can use
datetimes across different time zones.
DECLARE @Year int, @Month int, @Day int
DECLARE @Hour int, @Minutes int, @Seconds int
DECLARE @FractionsOfASecond int
DECLARE @HourOffSet int, @MinuteOffSet int
SET @Year = 2013
SET @Month = 09
SET @Day = 02
SET @Hour = 15
SET @Minutes = 57
SET @Seconds = 49
SET @FractionsOfASecond = 500
SET @HourOffSet = 7
SET @MinuteOffSet = 30
SELECT DATETIMEOFFSETFROMPARTS (@Year, @Month, @Day,
@Hour,
@Minutes, @Seconds, @FractionsOfASecond, @HourOffSet,
@MinuteOffSet, 3) AS MyTimeZone
Conversion
functions
SQL Server 2012 introduces some useful conversion
functions that will help you avoid errors when dealing with different data
types.
TRY_PARSE
The basic syntax of TRY_PARSE is as follows:
SELECT TRY_PARSE (string AS datatype)
Below each query return a null, as the value passed in the
string does not convert to a value of the data type specified as the second
parameter:
SELECT TRY_PARSE ('SQL Server 2012' AS datetime) AS
MyDateTime
SELECT TRY_PARSE ('SQL Server 2012' AS decimal) AS
MyDecimal
SELECT TRY_PARSE ('ABC' AS float) AS MyFloat
However, all of the following will return a date or
numeric value equivalent to the string value:
SELECT TRY_PARSE ('03-07-2012' AS datetime) AS MyDateTime
SELECT TRY_PARSE ('2012' AS decimal) AS MyDecimal1
SELECT TRY_PARSE ('2012.0' AS decimal) AS MyDecimal2
SELECT TRY_PARSE ('2012.0' AS float) AS MyFloat
TRY_PARSE looks like a solution in search of a problem,
but it is really handy for data cleansing and dirty data detection.
Example where data from a legacy system has to be loaded
into a varchar or nvarchar column as, although it should only contain integer
values, it actually has alphabetic characters in some of the rows:
CREATE TABLE #DataToBeCleansed
(
ID int IDENTITY (1,1),
IntegerDataToBeCleansed nvarchar(3)
)
GO
INSERT INTO #DataToBeCleansed (IntegerDataToBeCleansed)
VALUES ('1'),
('2'),
('E'),
('4'),
('5'),
('6'),
('L'),
('8'),
('9'),
('10')
GO
Table now contains the following values:
SELECT * FROM #DataToBeCleansed
-- finds those values which are not true integers
SELECT ID, IntegerDataToBeCleansed
INTO #RowsToBeCleansed
FROM #DataToBeCleansed
WHERE TRY_PARSE(IntegerDataToBeCleansed AS int) IS NULL
GO
SELECT * FROM RowsToBeCleansed
SELECT ID, IntegerDataToBeCleansed
INTO #CleansedData
FROM #DataToBeCleansed
WHERE TRY_PARSE(IntegerDataToBeCleansed as int) IS NOT
NULL
GO
SELECT * FROM #CleansedData
PARSE
We take one of the previous queries and modify it slightly.
SELECT ID, IntegerDataToBeCleansed
FROM #DataToBeCleansed
WHERE PARSE(IntegerDataToBeCleansed AS int) IS NULL
That the query fails when it hits the rows where the
string value cannot be parsed into an integer value.
Msg 9819, Level 16, State 1, Line 1
Error converting string value 'E' into data type int
using culture ''.
The culture setting for both PARSE and TRY_PARSE defaults
to the language of the current session on which the query is being executed.
This is most easily understood using the classic US/British datetime format:
SELECT PARSE ('01/03/2012' AS DATETIME USING 'en-GB') AS
GBDate
SELECT PARSE ('01/03/2012' AS DATETIME USING 'en-US') AS
USDate
IIF
IIF is an inline conditional statement, just like most modern
programming languages have had for years now. You pass an expression that can
evaluate to either true or false to the function and it returns one value for
true and another for false.
DECLARE @YourName nvarchar(20) = 'Larry'
DECLARE @MyName nvarchar(20) = 'Larry'
SELECT IIF (@YourName = @MyName, 'Same person.',
'Different people.') AS NameMatch
If @YourName and @MyName match, then the result will be Same
person. If they don't match, the result will be Different people:
Now let us pass in two different values and see how this
works:
DECLARE @YourName nvarchar(20) = 'Larry'
DECLARE @MyName nvarchar(20) = 'Colin'
SELECT IIF (@YourName = @MyName, 'Same person.',
'Different people.') AS NameMatch
Contd in next post
