This article will provide an introduction to SQL escape characters for using special characters and their characteristics.
Introduction
In SQL Server, MySQL, and other databases, data is stored in huge amounts. Relatively, it is being processed using various ways. A few of the basics are using queries, procedures, functions, and more. SQL developers usually use many special characters as well as wildcards in queries as an expression to form a wide variety of criteria. Some of the characters can’t be interpreted as “AS-IS” by the processing engine and thus special instruction needs to be added. This instruction will flag those characters to escape and ensure the process of correct characteristics. Thus, they are generally known as “Escape” characters. Most widely they are used with strings or text-based data processing. There are various ways of using escape characters. In this article, we will look at some of the examples to understand the use of SQL escape characters in detail.
Setting up a database instance
To understand the use of SQL escape characters, we need to set up an instance of a database that uses SQL as a query language and supports escape characters as well. One can set up any database of choice that supports this criterion. In this article, we will be using the MySQL database on Azure. It’s one of the most popular open-source databases and supports SQL as the query language and supports escape characters as well. To work with MySQL, we would need a query editor using which we would be executing SQL commands on the database instance. MySQL Workbench is a freely available IDE that works very well with MySQL. We will be using this tool for all the examples in this article. It is assumed that a MySQL database instance, as well as MySQL Workbench, is also installed.
Working with SQL escape characters
If you are working with a freshly created instance, it won’t have a new database created. It is advisable to create a separate database for this example. Though one can also opt to use an existing database instance. Data is hosted in tables and SQL queries would process or query data. So, we need to create at least one table with some sample data in it.
We need a table with at least one field that hosts data as text/varchar/string or similar text-based data types as most of these characters would work well with such datatypes. An employee is one of the very commonly found entities in any typical enterprise data model. Here, I have created an employee table with just one field name ename, for the sake of simplicity.
Shown below is a list of typical escape sequences or characters in MySQL. These are very standard SQL escape characters and not just specific to MySQL. While looking at this list, one may wonder where such characters may get used. For example, while querying JSON data or formatting JSON data for storing or storing any form of code, one may need to add a backslash. While storing addresses, one may want to format each line of the address in a new line, so that it can be readily displayed on the front end without the need for formatting it every time. The percent character “%” is a wild card character that is generally used in many functions that accepts regular expression patterns. One common example of such a function is the LIKE operator. Let’s say that we are storing mathematical formulas which include characters like +, -, and %. If we want to query a formula that involves the “%” character, we may end up using the LIKE operator and in the absence of the SQL escape character, we will face difficulty in easily querying the desired formulas using a regular expression pattern. So, there are a variety of use cases where SQL escape characters can be of great help. We will look at the use of some of these characters through some examples.
The backslash (\) character is used to form an escape sequence for all the special characters. This is a standard SQL coding convention. For some, it retains its original purpose, and for others, it introduces new functionality. Let’s see a few examples of both.
Let us start with simple examples. Let us say, we want to insert textual values in the ename field with some special characters. One may want to add a single quote in case of words like “I’ll”, or use “%” for mathematical formulas and likewise. To simulate different situations, let’s add a few textual values with special characters. If we use the below code, we will encounter an error as the query engine won’t be able to parse the syntax correctly.
|
1 2 3 |
INSERT INTO employee(ename) values('HenryIII'') |
Instead, we can use the escape sequence for the single quote as shown below to make it work
|
1 2 3 |
INSERT INTO employee(ename) values('HenryIII\'') |
The above code will ensure that the single inverted quote is taken as a simple string and not the starting/ending of any expression. Like the above example, let us say if you want to put double inverted quotes then the below code will not work. One may wonder why enclosed values with double quotes would be instead of a single quote as shown in the above example. Some developers prefer enclosing values in double quotes, and MySQL supports that as well. So, if a developer is using double quotes while executing the INSERT command as shown below, three double quotes would confuse the query engine to parse this command and we would encounter an error again.
|
1 2 3 |
INSERT INTO employee(ename) values(“HenryIV””) |
So instead, we can use the escape sequence as shown below for the extra double quote to make the instruction explicit for the query engine to parse the inner double quote as text instead of the closing quote. With the SQL escape sequence in the value, now the command would execute successfully, and the value would get inserted into the table as shown below.
|
1 2 3 |
INSERT INTO employee(ename) values(“HenryIV\””) |
Other special characters can be used similarly with a backslash to be treated like a simple character. Now let’s see some examples of functional characters.
Let’s say we want to store a name in two lines, then like HTML code we could use the ‘\n’ character. This is to simulate the example we discussed earlier, where one may need to store lines of an address in different lines. Below is a sample of storing a name in two lines
|
1 2 3 |
INSERT INTO employee(ename) values("Henry V \n Henry VI") |
And the output will be:
Now we can see in the above image that the last record is not in two lines. However, it is stored in two lines. To see the actual effect, I would recommend copying the last record into a text editor like notepad and it would look something like the below. The reason one won’t be able to see the data formatted in the below manner right in the result pane of the query editor is due to the reason that query editor shows data in a grid, where the formatting cannot be shown in multi-line or textarea kind of format.
In the above image, we can see the that second record after “\n” is in the second line. Let’s take another example. Let’s say in some cases we would want some space between two words. Similar to HTML we have ‘\t tab space which we can use below
However similar to earlier, it will have no effect in MySQL workbench but it will in notepad as below
Thus, there are escape characters that will have an impact and will provide functionality as well. Moving on next, developers mostly use the LIKE operator in the WHERE clause in which escape characters are used to fetch the appropriate results. Let’s take an example. In the below image, we have the first record which is “HenryI_”. Assume we would like to fetch the recording ending with the “_” character. Usually below code is expected to work,
|
1 2 3 4 |
Select * from employee where ename LIKE '%_' |
However, it will have below results rather than the correct record fetched. The reason is due to the “_” character not being considered correctly.
To get the results correctly, one should use backlash with an underscore as below:
|
1 2 3 4 |
Select * from employee where ename LIKE '%\_' |
Let’s take another example of using a second record as an example to fetch “HenryII%”. To get the exact record one must use below code for to retrieve the exact records.
|
1 2 3 4 |
Select * from employee where ename LIKE '%\%' |
Conclusion
- Use escape characters with wild characters and patterns to achieve a different purpose.
- Use backlash, single inverted quotes, and other operators as escape characters.
- Ensure checking of operator support with the database before using one.
He has worked internationally with Fortune 500 clients in various sectors and is a passionate author.
View all posts by Rahul Mehta
- Finding Duplicates in SQL - February 7, 2024
- MySQL substring uses with examples - October 14, 2023
- MySQL group_concat() function overview - March 28, 2023