Character and Binary Strings in SQL

Character Strings

Character strings are enclosed in apostrophes when used in SQL. If the character string contains the apostrophe character, it must be entered twice.

  SET DelimChars = '.,"()[]_-+';
  SET paper = 'Reader''s Digest';

Function that return the specified string enclosed in quotation marks.

  FUNCTION Enclose(IN str CHAR (100)) RETURNS CHAR(102);
  BEGIN 
   RETURN '"'||str||'"'; 
  END

The N character may be specified before the first apostrophe. This signals that the string may contain special international characters. This is ignored in 602SQL.

Binary Strings

Binary strings are entered in hexadecimal or as an order of bytes enclosed in apostrophes:

The X character must be specified before the first apostrophe in hexadecimal notation, and each byte is written as two hexadecimal numbers.
The B character must be specified before the first apostrophe in byte notation, and each byte is written as eight digits of 0's or 1's.

The following examples enter the same 2-byte string with the values 193 and 5: X'C105', B'1100000100000101'.

Notation on Parts

A string notation may be terminated with an apostrophe. There may be blank spaces or end of line symbols after the apostrophe, but if another apostrophe follows, the following string is appended to the original string.

Example:

'This' 'is'
'it!'

The above is equivalent to 'Thisisit!'. This can be used for transparent notation of a long string.

Characters that Cannot be Entered in Character Strings

There are characters that can be part of a character string, but cannot be directly entered, since they are not on the keyboard. You can use the Unicode escape sequence for entering these characters.

U& must always be before the identifier. Unicode escape sequence always begins with the opening Unicode Escape character and contains 4 hexadecimal digits.

The Unicode Escape character is usually the backslash. There are other possibilities, but you must specify the escape character at the end of the character string using the UESCAPE'unicode_escape_character' notation.

If the Unicode Escape character is part of the string in the original meaning, it must be entered twice.

Example: enter 'pociąg', 'połowa'

U&'poci\0105g', U&'po*0142owa'UESCAPE'*'

Variations from the Intermediate to Full Level

Binary and hexadecimal strings belong to the Full level.

602SQL Variations

Apart from apostrophe notation, character strings may also be enclosed in quotation marks if the appropriate compatibility attribute is set. However, this notation is not recommended because quotation marks have a different meaning in the SQL standard.

Notation in quotation marks.

  SET DelimChars = ".,""()[]_-+";
  SET paper = "Reader's Digest";

Function that returns the specified string enclosed in quotation marks.

  FUNCTION Enclose(IN str CHAR (100)) RETURNS CHAR(102);
  BEGIN 
   RETURN """"||str||""""; 
  END