Informix Guide to SQL: Syntax Segments

A quoted string is a string constant that is surrounded by quotation marks. Use the Quoted String segment whenever you see a reference to a quoted string in a syntax diagram.

You use quoted strings to specify string constants in data manipulation statements and other SQL statements. For example, you can use a quoted string in an INSERT statement to insert a value into a column with a character data type.

You must observe the following restrictions when you specify character in quoted strings:

• If you are using the ASCII code set, you can specify any printable ASCII character, including a single quote or double quote. For restrictions that apply to using quotes in quoted strings, see Using Quotes in Strings.
• If you are using a nondefault locale, you can specify non-ASCII characters, including multibyte characters, that the code set of your locale supports. For further information, see the discussion of quoted strings in the Informix Guide to GLS Functionality.
• If you enable newline characters for quoted strings, you can embed newline characters in quoted strings. For further information on how to enable newline characters for quoted strings, see Newline Characters in Quoted Strings.
• When you set the DELIMIDENT environment variable, you cannot use double quotes to delimit a quoted string. When DELIMIDENT is set, a string enclosed in double quotes is an identifier, not a quoted string. When DELIMIDENT is not set, a string enclosed in double quotes is a quoted string, not an identifier. For further information, see Using Quotes in Strings.
• You can enter DATETIME and INTERVAL data as quoted strings. For the restrictions that apply to entering DATETIME and INTERVAL data in quoted-string format, see DATETIME and INTERVAL Values as Strings.
• Quoted strings that are used with the LIKE or MATCHES keyword in a search condition can include wildcard characters that have a special meaning in the search condition. For further information, see LIKE and MATCHES in a Condition.
• When you insert a value that is a quoted string, you must observe a number of restrictions. For further information, see Inserting Values as Quoted Strings.

By default, the string constant must be written on a single line; that is, you cannot use embedded newline characters in a quoted string. However, you can override this default behavior in one of two ways:

• To enable newline characters in quoted strings in all sessions, set the ALLOW_NEWLINE parameter to 1 in the ONCONFIG file.
• To enable newline characters in quoted strings for a particular session, execute the built-in function, IFX_ALLOW_NEWLINE, within the session.

In the following example, the user enables newline characters in quoted strings for a particular session:

EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T')

If newline characters in quoted strings are not enabled for a session, the following statement is illegal and results in an error:

SELECT 'The quick brown fox

	jumped over the old gray fence'

	FROM customer
	WHERE customer_num = 101

However, if you enable newline characters in quoted strings for the session, the statement in the preceding example is legal and executes successfully.

For more information on the IFX_ALLOW_NEWLINE function, see IFX_ALLOW_NEWLINE Function. For more information on the ALLOW_NEWLINE parameter in the ONCONFIG file, see your Administrator's Reference.

The single quote has no special significance in string constants delimited by double quotes. Likewise, the double quote has no special significance in strings delimited by single quotes. For example, the following strings are valid:

"Nancy's puppy jumped the fence"

'Billy told his kitten, "No!"'

If your string is delimited by double quotes, you can include a double quote in the string by preceding the double quote with another double quote, as shown in the following string:

"Enter ""y"" to select this row"

When the DELIMIDENT environment variable is set, double quotes delimit identifiers, not strings. For more information on delimited identifiers, see Delimited Identifiers.

You can enter DATETIME and INTERVAL data in the literal forms described in the Literal DATETIME and Literal INTERVAL, or you can enter them as quoted strings. Valid literals that are entered as character strings are converted automatically into DATETIME or INTERVAL values. The following INSERT statements use quoted strings to enter INTERVAL and DATETIME data:

INSERT INTO cust_calls(call_dtime) VALUES ('1997-5-4 10:12:11')

INSERT INTO manufact(lead_time) VALUES ('14')

The format of the value in the quoted string must exactly match the format specified by the qualifiers of the column. For the first case in the preceding example, the call_dtime column must be defined with the qualifiers YEAR TO SECOND for the INSERT statement to be valid.

Quoted strings with the LIKE or MATCHES keyword in a condition can include wildcard characters. For a complete description of how to use wildcard characters, see Condition.

If you are inserting a value that is a quoted string, you must adhere to the following conventions:

• Enclose CHAR, VARCHAR, NCHAR, NVARCHAR, DATE, DATETIME, and INTERVAL values in quotation marks.
• Set DATE values in the mm/dd/yyyy format or in the format specified by the DBDATE environment variable, if set.
• You cannot insert strings longer than 256 bytes.
• You cannot insert strings longer than 32 kilobytes.
• Numbers with decimal values must contain a decimal point. You cannot use a comma as a decimal indicator.
• You cannot precede MONEY data with a dollar sign ($) or include commas.
• You can include NULL as a placeholder only if the column accepts null values.

For a discussion of the DELIMIDENT environment variable, see the Informix Guide to SQL: Reference.

For a discussion of the GLS aspects of quoted strings, see the Informix Guide to GLS Functionality.