BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This blog summarises the main new features of SQL Server for non-administrators (that is, most of us). Most parts of SQL Server get minor changes at best, but SSAS Tabular 2017 gets a host of major improvements.
- What's new in SQL Server 2017 (differences versus 2016)
- Changes to SQL Server 2017 installation
- What's new in the T-SQL language in SQL Server 2017 (this blog)
- What's new in Integration Services 2017 (SSIS 2017)
- What's new in Reporting Services 2017 (SSRS 2017)
- What's new in Analysis Services 2017 (SSAS 2017)
Posted by Andy Brown on 23 January 2018
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
What's new in the T-SQL language in SQL Server 2017?
Not much! I've listed the new functions under separate headings below.
At last: the TRIM function!
There is one new function in SQL Server 2017 which is way, way overdue - TRIM!

At long, long last - if you want to strip trailing and leading blanks from a string of text, you no longer have to combine RTRIM and LTRIM. SQL Server enters the 21st century!
Using STRING_AGG to join column values together
The string_agg function makes it much easier to combine values of a column from different rows:

The function even supports Intellisense when choosing the column name whose values you want to combine.
So if you have a table like this called tblOwl:

This table contains the names of 5 owls.
Then you could run this query:
-- combine all values of first 3 owls
SELECT
string_agg(OwlName,',') AS Owls
FROM
tblOwl
WHERE
OwlId <= 3
This would show the first 3 column values, joined together with a comma:

The result of running the above query.
You used to be able to do this by messing about with XML file paths - I for one won't miss this fudge!
Using CONCAT_WS to concatenate with separators
The CONCAT_WS function allows you to join bits of text together, and ignores nulls:
-- show name of Wise Owl
SELECT CONCAT_WS(
'_',
'Wise',
Null,
'Owl',
Null,
'Training'
)
So the query above would show this output:

The null values are ignored.
Use TRANSLATE to replace one string pattern with another
This function is best explained by an example:
-- change brackets and extension letter
-- in a phone number
SELECT
TRANSLATE(
'+44 (1457) 858877 x 2199',
'() x',
'[] X'
)
This query will display:

The query makes 3 replacements, as shown below.
The query replaces ( with [, ) with ] and x with X, avoiding the need to use a series of REPLACE functions.
The fact that the search string and replacement string have to be the same length makes this function of limited use.
- What's new in SQL Server 2017 (differences versus 2016)
- Changes to SQL Server 2017 installation
- What's new in the T-SQL language in SQL Server 2017 (this blog)
- What's new in Integration Services 2017 (SSIS 2017)
- What's new in Reporting Services 2017 (SSRS 2017)
- What's new in Analysis Services 2017 (SSAS 2017)