Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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! |
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.
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.