SQL Server 2017+ How To Replace Multiple Characters In A Single Statement With TRANSLATE

Jul 5, 2017

The REPLACE function in SQL Server has until now been quite limited. SQL Server 2017 has introduced a new TRANSLATE function that addresses many of these shortcomings, namely the need to daisy chain replace calls to replace multiple characters.

These two functions are however quite different in how they work and what they output.

  • REPLACE will replace all occurrences of a set of characters with another set of characters.
  • TRANSLATE will one by one replace each character in the match expression with the character in that same position on the replace side. This means both expressions need to be the same length

Let’s look at some examples…

SELECT REPLACE('testinf','testinf','testing')
SELECT TRANSLATE('testinf','testinf','testing')

In the above example both functions will return ‘testing’, so what’s so good about translate? Let’s take something a bit more complex so we can see when translate makes more sense. Imagine we want to swap all periods and commas with a space…

Using REPLACE that would look like this…

SELECT REPLACE(REPLACE('testing, 123.','.',' '),',',' ')

Using replace for this gets pretty ugly as we have to chain replace methods. I’ve seen procedures that chain several or more replaces to clean data. Let’s look at how this is done with replace…

SELECT TRANSLATE('testing, 123.','.,','  ')

Much neater, each character in the match expression is matched with a space on the replace side and swapped out.

Level Up Your SQL Server Cross Platform Skills With mssql-scripter

Jun 27, 2017

The ability to export a script of database objects has for a long time been a feature of SQL Server Management Studio. With the new OSS cross platform tooling from Microsoft we can now have ths feature everywhere. mssql-scripter allows you to export schema and or data from SQL Server from pretty much any terminal

I do a lot of my SQL Server sandbox stuff on macOS using a Docker image of SQL Server 2017 with VS Code and the mssql plugin as my editor. With the new mssql-scripter tool I can now export SQL scripts of my database objects and data directly from the VS Code terminal.

For example here is my VS Code window listing all the databases in my Docker hosted SQL Server 2017 server…

List Database In VS Code

From here I can open the terminal in VS Code and run the mssql-scripter tool to generate a new SQL Script called InitialDb.sql in my working directory…

MSSQL-Scripter From VS Code

Once this has run I get my new script file and inside is the SQL Script to create that database and all it’s objects…

DB Creation Script

This tool supports a great deal of arguments for specifying what you want to script from the database. For example we can Script Schema only, data only, Schema and Data then we can go down to exactly what types of objects to script for example Statistics, logins etc….

To get a full list of what mssql-scripter supports at the console run…

mssql-scripter –help

Here’s a small set of the arguments that –help lists…

mssql-scripter arguments

Instructions for installing mssql-scripter on each platform can be found on their GitHub installation page

SQL Server 2017 Concatenation With STRING_AGG

Jun 20, 2017

One of the new less publicized features in SQL Server 2017 is STRING_AGG. This new feature can take an expression of string values and concatenate them with a specified separator.

There’s not much more to say about it other than that really, so lets look at an example. Imagine we have a user table and we want to provide our application with a comma separated list of all the usernames in the table…

    Username NVARCHAR(20)

INSERT INTO dbo.[User](Username)

SELECT STRING_AGG(ISNULL(Username,'Not Specified'),' , ') 
FROM dbo.[User]

This kind of concatenation has always been a bit of a pain before SQL Server 2017, usually achieved using xml methods or variables and coalesce, now it couldnt be any easier.

subscribe via RSS