InterSystems SQL Reference
A collation function that sorts values as strings.
||A string expression, which can be the name of a column, a string literal, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2). expression can be a subquery.
||Optional A positive integer, which specifies that the collated value will be truncated to the value of maxlen. Note that maxlen includes the appended leading blank space. You can enclose maxlen with double parentheses to suppress literal substitution: ((maxlen)).
to format that is sorted as a (case-sensitive) string. %SQLSTRING
strips trailing whitespace (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. This appended blank space forces NULL and numeric values to be collated as strings. Leading and trailing zeros are removed from numbers.
appends a blank space to all values, it collates a NULL
value as a blank space, with a string length of 1. %SQLSTRING
collates any value containing only whitespace (spaces, tabs, and so on) as the SQL empty string
(''). When %SQLSTRING
appends a blank space to an empty (zero-length) string, it collates as a blank space plus the internal representation of an empty string, $CHAR(0), resulting in a string length of 2.
The optional maxlen
argument truncates the expression
string to the specified number of characters when indexing or collating. For example, if you insert a string with maxlen
truncation, the full string is inserted and can be retrieved by a SELECT
statement; the index global for this string is truncated to the specified length. This means that ORDER BY
and comparison operations only evaluate the truncated index string. Such truncation is especially useful for indexing on strings that exceed the 255-character limit for InterSystems IRIS subscripts. When converting from non-InterSystems IRIS systems, some users encountered problems when they indexed on a VARCHAR(255) field and then tried to insert data into the table. With the maxlen
argument, if you need to index on a long field, you can use the truncation length parameter.
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",8)
This function can also be invoked from ObjectScript using the SQLSTRING()
WRITE $SYSTEM.SQL.SQLSTRING("The quick, BROWN fox.")
Both of these methods support truncation after SQLSTRING conversion. Note that the truncation length must include the appended blank:
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",8,6),!
WRITE $SYSTEM.SQL.SQLSTRING("The quick, BROWN fox.",6)
For a not case-sensitive string conversion, refer to %SQLUPPER
To change the system-wide default collation from %SQLUPPER (which is not case-sensitive) to %SQLSTRING (which is case-sensitive), use the following command:
After issuing this command, you must purge indexes, recompile all classes, then rebuild indexes. Do not rebuild indices while the table’s data is being accessed by other users. Doing so may result in inaccurate query results.
The following query uses %SQLSTRING
in the WHERE
clause to perform a case-sensitive select:
SELECT Name FROM Sample.Person
WHERE %SQLSTRING Name %STARTSWITH %SQLSTRING 'Al'
ORDER BY Name
The following example uses %SQLSTRING
with a string truncation to return the first two characters of each name. Note that the string truncation is 3 (not 2) because of the leading blank added by %SQLSTRING
. The ORDER BY
clause uses this two-character field to put the rows in a rough collation sequence:
SELECT Name, %SQLSTRING(Name,3) AS FirstTwo
ORDER BY FirstTwo
This example returns the truncated values without changing the case of letters.
The following example applies %SQLSTRING
to a subquery:
SELECT TOP 5 Name, %SQLSTRING((SELECT Name FROM Sample.Company),10) AS Company
Content Date/Time: 2019-07-18 06:47:30