Skip to main content

%TRUNCATE (SQL)

A collation function that truncates a string to the specified length and applies EXACT collation.

Synopsis

%TRUNCATE(expression[,length])

Description

%TRUNCATE truncates expression to the specified length, then returns it in the EXACT collation sequence.

EXACT collation orders pure numeric values (values for which x=+x) in numeric order first, followed by all other characters in string collation sequence. The EXACT string collation sequence is the same as the ANSI-standard ASCII collation sequence: digits are collated before uppercase alphabetic characters and uppercase alphabetic characters are collated before lowercase alphabetic characters. Punctuation characters occur at several places in the sequence.

%TRUNCATE passes through NULLs unchanged.

%TRUNCATE is an InterSystems SQL extension and is intended for SQL lookup queries.

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

  WRITE $SYSTEM.SQL.Functions.TRUNCATE("This long string",9)

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

length

An optional argument denoting the truncation length, specified as an integer. The initial length characters of expression are returned. If you omit length, %TRUNCATE collation is identical to %EXACT collation. You can enclose length with double parentheses to suppress literal substitution: ((length)).

Examples

The following example uses %TRUNCATE to return the first four characters of Name values:

SELECT TOP 5 Name,%TRUNCATE(Name,4) AS ShortName
FROM Sample.Person

The following example applies %TRUNCATE to a subquery:

SELECT TOP 5 Name, %TRUNCATE((SELECT Name FROM Sample.Company),10) AS Company
FROM Sample.Person

The following example uses %TRUNCATE in the GROUP BY clause to create an alphabet list that returns the number of names that begin with each letter:

SELECT Name AS FirstLetter,COUNT(Name) AS NameCount 
FROM Sample.Person GROUP BY %TRUNCATE(Name,1) ORDER BY Name 

The following two examples show how %TRUNCATE performs EXACT collation. The ORDER BY in the first example truncates Home_Street to two characters. Because the first two characters of a street address are almost always numbers, the Home_Street fields are ordered in the numeric sequence of their first two numbers.

SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %TRUNCATE(Home_Street,2)

The ORDER BY in the second example truncates Home_Street to four characters. Because the fourth character of some street addresses is not a number (a blank space, for example), the Home_Street values that begin with four (or more) numbers are ordered first in numeric sequence, then the Home_Street values that contain a non-numeric character within the first four characters are ordered in string sequence:

SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %TRUNCATE(Home_Street,4)

See Also

FeedbackOpens in a new tab