Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page:

Escape Special Characters

Description

CUBRID supports two kinds of methods to escape special characters. One is using quotes and the other is using backslash (\).

Escape with Quotes

If you set no for the system parameter ansi_quotes in the cubrid.conf file, you can use both double quotes (") and singe quotes (') to wrap strings. The default value for the ansi_quotes parameter is yes, and you can use only single quotes to wrap the string. The numbers 2 and 3 below are applied only if you set for the ansi_quotes parameter to no.

  1. You should use two single quotes ('') for the single quotes included in the strings wrapped in single quotes.
  2. You should use two double quotes ("") for the double quotes included in the strings wrapped in double quotes.
  3. You don't need to escape the single quotes included in the string wrapped in double quotes.
  4. You don't need to escape the double quotes included in the string wrapped in single quotes.
Escape with Backslash

You can use escape using backslash (\) only if you set no for the system parameter no_backslash_escapes in the cubrid.conf file. The default value for the no_backslash_escapes parameter is yes. Depending on the input value, the following are the special characters.

  • \' : Single quotes (')
  • \" : Double quotes (")
  • \n : Newline, linefeed character
  • \r : Carriage return character
  • \t : Tab character
  • \\ : Backslash
  • \% : Percent sign (%). For details, see the following description.
  • \_ : Underbar (_). For details, see the following description.

For all other escapes, the backslash will be ignored. For example, "\x" is the same as entering only "x".

\% and \_ are used in the pattern matching syntax such as LIKE to search percent signs and underbars and are used as a wildcard character if there is no backslash. Outside of the pattern matching syntax, "\%"and "\_" are recognized as normal strings not wildcard characters. For details, see LIKE Predicate.

Example 1

The following is the result of executing Escape if a value for the system parameter ansi_quotes in the cubrid.conf file is no, and a value for no_backslash_escapes is no.

SELECT STRCMP('single quotes test('')', 'single quotes test(\')');

 

   strcmp('single quotes test('')', 'single quotes test('')')

=============================================================

                                                            0

 

SELECT STRCMP("\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z", "a\bcdefghijklm\nopq\rs\tuvwxyz");

 

   strcmp('abcdefghijklm

s       uvwxyz', 'abcdefghijklm

s       uvwxyz')

=====================================================================

                                                                    0

 

SELECT LENGTH('\\');

 

   char_length('\')

===================

                  1

Example 2

The following is the result of executing Escape if a value for the system parameter ansi_quotes in the cubrid.conf file is yes, and a value for no_backslash_escapes is yes.

SELECT STRCMP('single quotes test('')', 'single quotes test(\')');

 

In the command from line 2,

ERROR: unterminated string

 

In the command from line 2,

ERROR: syntax error, unexpected UNTERMINATED_STRING

 

 

SELECT STRCMP("\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z", "a\bcdefghijklm\nopq\rs\tuvwxyz");

 

In line 1, column 18,

ERROR: [\a\b\c\d\e\f\g\h\i\j\k\l\m\n\o\p\q\r\s\t\u\v\w\x\y\z] is not defined.

 

In line 1, column 18,

ERROR: [a\bcdefghijklm\nopq\rs\tuvwxyz] is not defined.

 

SELECT LENGTH('\\');

 

   char_length('\\')

====================

                   2

Example 3

The following is the result of executing Escape if a value for the system parameter ansi_quotes in the cubrid.conf file is yes, and a value for no_backslash_escapes is no.

CREATE TABLE t1 (a varchar(200));

INSERT INTO t1 VALUES ('aaabbb'), ('aaa%');

 

SELECT a FROM t1 WHERE a LIKE 'aaa\%' escape '\\';

 

  a

======================

  'aaa%'