Format Code Now
/

SQL Escape / Unescape

ED-62

Escape a value so it fits in a SQL string literal, unescape a literal back to plain text, or escape the wildcards in a LIKE pattern.

Runs in your browser. Nothing is uploaded.

Text or SQL string literal

Result

How to use it

  1. Paste the text, or the SQL string literal, into the input box.
  2. Pick the dialect: T-SQL, MySQL or PostgreSQL. Decide whether to add the surrounding quotes and an N or E prefix.
  3. Click Escape, Unescape or Escape LIKE wildcards.
  4. Copy the result into your script, or download it as a .txt file.

How each dialect escapes

T-SQL and standard PostgreSQL strings double each single quote, so O'Brien is written O''Brien. MySQL with its default settings uses backslash escapes, so backslashes, both quote marks, NUL, line breaks and Ctrl+Z are escaped with a backslash. The prefix option writes N'...' for T-SQL, which keeps non-ASCII text in an nvarchar value, and E'...' for PostgreSQL, which turns backslash escapes on.

Escaping LIKE wildcards

In a LIKE pattern, % and _ are wildcards. To match them literally you escape them. This button puts a backslash before %, _ and the backslash itself, and before [ for T-SQL, then escapes the result for the string literal in your chosen dialect. In T-SQL you must add ESCAPE '\' after the pattern for the backslash to count. MySQL and PostgreSQL treat backslash as the default LIKE escape.

Not a substitute for parameters

Escaping is useful for one-off scripts, data fixes and test data. It is not the right way to put user input into a query in an application, where parameterized queries are the safe approach. Settings such as MySQL NO_BACKSLASH_ESCAPES and PostgreSQL standard_conforming_strings off change how backslashes are read, so check them before you rely on the output.

Error messages and what to do

MessageWhat it meansFix
is not doubledWhen unescaping, a single quote inside the literal is not written as two quotes.Write each quote as '' or check the dialect.
The string literal is never closedThe text starts with a quote but has no closing quote.Add the closing single quote.
PostgreSQL text cannot contain the NUL characterPostgreSQL cannot store U+0000 in text.Remove the NUL character.

Questions people ask

How do I escape a single quote in SQL?

In T-SQL and PostgreSQL, write two single quotes in a row, so it's becomes it''s. In MySQL with default settings you can use a backslash, as in it\'s, or also double the quote. Paste the text here and pick the dialect.

How do I escape % and _ in a LIKE query?

Put a backslash in front of them and add the ESCAPE clause where the database needs it. Escape LIKE wildcards does the first part for your chosen dialect. T-SQL needs ESCAPE '\' after the pattern.

What does the N prefix do in T-SQL?

N'...' marks the string as Unicode (nvarchar). Without it, non-ASCII characters in a literal can be converted using the database code page and turn into question marks. Tick the prefix option to add it.

Does this prevent SQL injection?

No. Escaping text by hand is error-prone and depends on database settings. For application code use parameterized queries. This tool is for scripts, data fixes and examples where you are writing literals yourself.

How do I unescape a SQL string?

Paste the literal, with or without its quotes and N or E prefix, and click Unescape. Doubled quotes become single quotes and, for MySQL or E strings, backslash escapes are decoded.

Why does MySQL give a different result?

MySQL by default treats the backslash as an escape character inside strings, so backslashes and quotes are escaped with a backslash. T-SQL and standard PostgreSQL do not, so they only double the single quote.

About this tool

T-SQL and standard PostgreSQL strings double each single quote. MySQL with default settings uses backslash escapes instead, so backslashes, both quote marks, NUL, line breaks and Ctrl+Z are escaped. Escape LIKE wildcards first puts a backslash before %, _ and the backslash itself (and [ for T-SQL), then escapes the result as a string for the dialect you chose. In T-SQL you must add ESCAPE '\' after the pattern for that backslash to count, and MySQL and PostgreSQL use backslash by default. This is for one-off scripts and data fixes, not for building queries from user input; parameterized queries are the safe way to do that. NO_BACKSLASH_ESCAPES in MySQL and standard_conforming_strings off in PostgreSQL change how backslashes are read.

Format Code Now: free, no ads, no accounts. Your input stays in your browser.