This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

New SQL Language Features for SQL Server 2022
SQL Server 2022 is now in preview (you can download an evaluation copy). It includes new functions to split strings, trim text and truncate dates, among other things.

Posted by Andy Brown on 06 October 2022

In this blog

Trimming Text
Truncating dates using DATETRUNC

New SQL Language Features for SQL Server 2022

You can now download an evaluation copy of SQL Server 2022 (internal version 16.x) - so I did!

You can see all of the changes for the new version - and also a download link for an evaluation copy - at this Microsoft site.

The interesting new language features are as follows:

Feature	Notes
Splitting strings	The website announces the STRING_SPLIT function, which allows you to turn a comma-delimited string of values into a table. I got really excited about this, but then realised I'd written a full blog announcing this back in March 2021!
Trimming text	You can now specify which characters you want to trim from the front or back of a string of text (see below for more on this).
Truncating dates	You can now choose to what extent you want to simplify a date or date/time (for example, you could just pick out the year, or the year/month). The only way to understand this is with examples, which I've included below.
JSON functions	There are new functions to check if a string of text is in JSON format and to generate JSON objects, but with so many better tools around for importing and exporting JSON it's hard to see you doing this in SQL Server Management Studio.
Time series functions	You can now generate a series of numbers and also pick out the last day, week, month, etc in a time interval, but again it's hard to see why you would ever choose to work with time series data in SQL!

Trimming Text

The updated LTRIM, RTRIM and TRIM functions allow you to specify which characters you want to remove from the beginning and end of a string of text. For example, consider this query:

SELECT

f.FilmName,

trim(BOTH 'e ht' FROM f.FilmName) AS TrimmedTitle

FROM

tblFilm AS f

WHERE

f.FilmName like 'the%'

ORDER BY

f.FilmName DESC

This might give these results for a table of films:

We've tried to remove the The from the start of each film (it didn't matter which order we put the characters in, as they'll each be removed separately), but this has played havoc with Terminator!

Obviously in the above example we could have avoided lopping characters off the end of film names by using LTRIM, or solved the problem more reliably using the replace function.

Truncating dates using DATETRUNC

The DATETRUNC function is similar to the EOMONTH function in Excel, but has two differences: it picks out the start of a period and not the end of it, and you can use it for any time period (from microseconds all the way up to years).

Here's an example of the use of DATETRUNC to show film release dates:

-- date format used

DECLARE @df varchar(max) = 'ddd dd MMM yyyy'

SELECT

f.FilmName,

-- the release date of this film

FORMAT(f.FilmReleaseDate,@df) AS ReleaseDate,

-- truncating the month

FORMAT(DATETRUNC(month,f.FilmReleaseDate),@df) AS FirstDayOfMonth,

-- truncating the quarter

FORMAT(DATETRUNC(quarter,f.FilmReleaseDate),@df) AS FirstDayOfQuarter,

-- truncating the year

FORMAT(DATETRUNC(year,f.FilmReleaseDate),@df) AS FirstDayOfYear

FROM

tblFilm AS f

Here's the start of the output this might give:

For each film you see its release date, the start of this month, the start of this quarter and the start of this year.

This is probably the update's most useful function (you can subtract one day from the start of a period to get the end of the preceding one), but it has a strange name! Why not call it StartOfPeriod, or something like that?

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

New SQL Language Features for SQL Server 2022

Trimming Text

Truncating dates using DATETRUNC

Head office

London

Manchester