How to use STRING ESCAPE in Sql Server

Intro

SQL Server provides the STRING_ESCAPE function to escape special characters in a string.. In this article, we will learn how to use STRING_ESCAPE in SQL Server.

The Syntax

The basic syntax of a STRING_ESCAPE is as follows:

STRING_ESCAPE(string, type)

• string: the string to scape characters
• type: rules to apply. 'json' is the only valid option now.

Below is a list of characters that can be escaped.


The list comes from the official docs: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql?view=sql-server-ver16#return-types

Getting Setup

For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/

Now create a file called docker-compose.yml and add the following.

version: "3.9"
services:
  db:
    image: "mcr.microsoft.com/mssql/server"
    ports: 
      - 1433:1433
    environment:
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

Creating a Table

Here is an example of escaping a tab.

SELECT STRING_ESCAPE('\ ', 'json') AS result;