SQL Server 2017 – TRIM, at last!

AzureSQLLogoThere is loads of good stuff in SQL Server 2017, run on Linux, Graph Databases, Python, but some times it is the small things in the daily T-SQL grind that really make the difference. The Trim function is one of these.

Cleaning up dirty data from source systems, well I say source systems, most likey xls, xlsx, csv and tsv files, your going to come across leading or trailing spaces, the ghosts in the string!

So for example if we had ‘   Some string from some funky csv export   ‘ with 3 leading and 3 trailing spaces we had to do this in SQL Server 2016 and below:

DECLARE @str AS VARCHAR(50)
SET @str = '  Some string from some funky csv export   '

SELECT RTRIM(LTRIM(@str)) AS TrimExample

Which can be converted to a UDF so you could just do your own TRIM function, but it would look untidy with a schema in front of it, blah!

DECLARE @str AS VARCHAR(50)
SET @str = ' Some string from some funky csv export '
SELECT udf.TRIM(@str) AS TrimExample

But now you can just do

DECLARE @str AS VARCHAR(50)
SET @str = ' Some string from some funky csv export '
SELECT TRIM(@str) AS TrimExample

No more having to recall which is your left and right, nice and tidy! Worth updating just for this function IMHO.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s