Skip to main content

%SQLSTRING (SQL)

A collation function that sorts values as strings.

Synopsis

%SQLSTRING(expression[,maxlen])

%SQLSTRING expression

Description

%SQLSTRING converts expression 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.

Because %SQLSTRING 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 maximum character length for InterSystems IRIS subscripts. With the maxlen argument, if you need to index on a long field, you can use the truncation length parameter.

%SQLSTRING performs maxlen truncation after converting expression; if maxlen exceeds the length of the converted expression no padding is added. Note that within InterSystems IRIS, no string can exceed the string length limit. No maximum is enforced for maxlen explicitly but InterSystems IRIS will issue a <MAXSTRING> error if applicable.

You can perform the same collation conversion in ObjectScript using the Collation()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab class:

  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",8)

This function can also be invoked from ObjectScript using the SQLSTRING()Opens in a new tab method call:

  WRITE $SYSTEM.SQL.Functions.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.

Note:

To change the system-wide default collation from %SQLUPPER (which is not case-sensitive) to %SQLSTRING (which is case-sensitive), use the following command:

  WRITE $$SetEnvironment^%apiOBJ("collation","%Library.String","SQLSTRING")

After issuing this command, you must purge indexes, recompile all classes, then rebuild indexes. Do not rebuild indexes while the table’s data is being accessed by other users. Doing so may result in inaccurate query results.

Arguments

expression

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 VARCHAR). expression can be a subquery.

maxlen

An optional date or timestamp expression from which the day of the month value is to be returned. An expression that is the name of a column, the result of another scalar function, or a date or timestamp literalA 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)).

Examples

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

By default, %STARTSWITH string comparisons are not case-sensitive. This example uses the %SQLSTRING format to make this comparison case-sensitive. It returns all names that begin with “Al” (such as Allen, Alton, etc.). Note when using %STARTSWITH, you should apply %SQLSTRING collation to both sides of the statement.

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
FROM Sample.Person
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
FROM Sample.Person

See Also

FeedbackOpens in a new tab