%STARTSWITH (SQL)
Synopsis
column %STARTSWITH substring
Description
-
column %STARTSWITH substring selects data values from a column that begin with the characters specified in substring. If substring does not match any column values, %STARTSWITH returns the null string. %STARTSWITH performs this match on the logical, internal storage value of the column, regardless of the display mode set.
You can use %STARTSWITH in any predicate condition of an InterSystems SQL query. For more details on predicate conditions, see Overview of Predicates.
This statement selects all names that begin with the letter M.
SELECT Name FROM Sample.MyTest WHERE Name %STARTSWITH 'M'
For other ways of matching a value, see Other Equivalence Comparisons.
Examples:
Arguments
column
A data column in a table whose values are being compared with substring. This argument can also be a scalar expression that evaluates to a column table, such as %EXTERNAL(column) or %SQLUPPER(column).
substring
The first character or characters to match with values in column. This argument must be an expression that resolves to a string or numeric value.
Examples
Select Column Data Based on Initial Characters
The %STARTSWITH predicate can process a variety of string and numeric types.
Letters
This statement returns one row for each distinct Home_State name that begins with the letter M.
SELECT DISTINCT Home_State FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'
ORDER BY Home_State
Under the default collation settings, %STARTSWITH matches are not case-sensitive, so this statement matches names beginning with either "M" or "m". For more details on controlling the case-sensitivity of matches, see Manage Case-Sensitivity of Selections Based on Collation Type.
Numbers
This statement uses a HAVING clause to select rows for people whose age starts with a 2. The result set displays the average for all ages and the average for the ages selected by the HAVING clause. It orders the results by age.
SELECT Name,
Age,
AVG(Age) AS AvgAge,
AVG(Age %AFTERHAVING) AS Avg20
FROM Sample.Person
HAVING Age %STARTSWITH 2
ORDER BY Age
Dates
This statement performs a %STARTSWITH comparison with the internal date format value for the DOB (date of birth) field. In this case, it select all dates from 11/5/1988 ($H=54000) through 08/1/1991 ($H=54999):
SELECT Name,DOB
FROM Sample.Person
WHERE DOB %STARTSWITH 54
ORDER BY DOB
Lists
If column contains a list collection, %STARTSWITH can use the %EXTERNAL format transformation function to compare the list values to substring. For example, this statement matches on rows in which the FavoriteColors list column begins with 'Bl':
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'Bl'
For list collections, when %EXTERNAL converts a list to DISPLAY format, the displayed list items appear to be separated by a blank space. This “space” is actually the two non-display characters CHAR(13) and CHAR(10). To use %STARTSWITH with more than one element in the list, you must specify these characters:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'Orange'||CHAR(13)||CHAR(10)||'B'
If column contains data of type %Libary.List, you do not need to use the %EXTERNAL, because %Libary.List data is stored in the LOGICAL format and does not have a separate DISPLAY format. To match list data, you can use $LIST functions. For example:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FavoriteColors %STARTSWITH $LISTFROMSTRING('Yellow,Orange')
Because InterSystems SQL stores lists as concatenated strings, you cannot use %STARTSWITH to match on elements in the middle of a list. %STARTSWITH matches only from the start of the list. This applies to both list collections and %Libary.List data.
Leading and Trailing Blanks
In most cases, %STARTSWITH treats leading blanks the same as any other character. For example, %STARTSWITH ' B' selects column values with exactly one leading blank followed by the letter B. However, a substring containing only blanks selects non-null values, not leading blanks.
The %STARTSWITH behavior with trailing blanks depends on the data type and collation type:
-
%STARTSWITH ignores trailing blanks in a string substring with SQLUPPER collation.
-
%STARTSWITH does not ignore trailing blanks in a numeric, date, or list substring.
In this statement, %STARTSWITH restricts the result set to names that begin with 'M'. Because Name is an SQLUPPER string data type, the trailing blanks in the substring are ignored.
SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH 'M '
In this statement, %STARTSWITH eliminates all rows from the result set because the trailing blanks in the substring are not ignored for a numeric value:
SELECT Name,Age FROM Sample.Person
WHERE Age %STARTSWITH '6 '
In this statement, %STARTSWITH eliminates all rows from the result set because the trailing blank in the substring is not ignored for a list value:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'Blue '
However, in this statement, the result set consists of those list values that start with Blue followed by a list delimiter, which is displayed as a blank space. In other words, this statement matches on lists beginning with ‘Blue’ that contain more than one item:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'Blue'||CHAR(13)||CHAR(10)
Select Column Data Using Logical Operators
The %STARTSWITH function supports the logical operators NOT, AND, and OR.
This statement selects all names that do not begin with the letter M.
SELECT Name FROM Sample.MyTest WHERE NOT Name %STARTSWITH 'M'
This statement selects all names that begin with M or N.
SELECT Name FROM Sample.MyTest WHERE Name %STARTSWITH 'M' OR Name %STARTSWITH 'N'
This statement selects all names in which the first names begin with M and the last names begin with N.
SELECT FirstName,LastName FROM Sample.MyTest WHERE FirstName %STARTSWITH 'M' AND LastName %STARTSWITH 'N'
Filter Out Null Values
In the %STARTSWITH function, if column evaluates to a non-null data value and substring is an empty value, then %STARTSWITH returns the non-null column data. You can use this behavior to filter out non-null values. For example, this statement restricts the result set to non-null values in the FavoriteColors column.
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FavoriteColors %STARTSWITH NULL
An empty substring can be any of these values:
-
NULL
-
CHAR(0)
-
the empty string ('')
-
a string consisting of only blank spaces (' ')
-
CHAR(32), which is the space character
-
CHAR(9), which is the tab character
These statements return the same results as the previous statement.
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FavoriteColors %STARTSWITH ''
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FavoriteColors %STARTSWITH ' '
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FavoriteColors %STARTSWITH CHAR(9)
If column evaluates to null and substring is an empty value, %STARTSWITH does not return data from column.
To return column values that consist of only whitespace characters, you must use %EXACT collation. Note that the %EXTERNAL collation type is not used for column when filtering nulls from a list field.
%STARTSWITH NULL and empty string behavior differs with a compound substring, because of the definitions of NULL and empty string. When you concatenate a value with NULL, the result is NULL. When you concatenate a value with the empty string, the result is the value. This is shown in the following examples:
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'B'||NULL
/* Selects all non-null rows */
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors) %STARTSWITH 'B'||''
/* Selects all values that begin with B */
Manage Case-Sensitivity of Selections Based on Collation Type
%STARTSWITH uses the same collation as the field it is matched against. Since string data type fields are defined with the SQLUPPER collation, which is not case-sensitive, the default %STARTSWITH selections are also not case-sensitive. For example, this statement matches home states beginning with either "M" or "m".
SELECT DISTINCT Home_State FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'
ORDER BY Home_State
If you assign a different collation type to the column in the WHERE clause, this collation type is matched to the literal value of the %STARTSWITH substring. For example, if you specify the search column, Home_State, to use EXACT (case-sensitive) collation, then %STARTSWITH matches only on home states beginning with "M".
SELECT DISTINCT Home_State FROM Sample.Person
WHERE %EXACT(Home_State) %STARTSWITH 'M'
ORDER BY Home_State
Some collation functions prepend a space character to a field value. This can cause %STARTSWITH to match no values, unless you apply an equivalent collation function to the substring.
For example, suppose a table contains a column, ExactName, that uses EXACT collation. If you apply SQLUPPER collation to ExactName within the column argument of %STARTSWITH, then %STARTSWITH searches a column whose values all start with a space character. Therefore, a comparison such as this one returns no rows:
SELECT ExactName FROM Sample.MyTest WHERE %SQLUPPER(ExactName) %STARTSWITH 'Ra'
To resolve this issue, you must prepend a space character to the substring, such as by applying the same collation function to the substring. This example applies a case-insensitive match to an EXACT column:
SELECT ExactName FROM Sample.MyTest WHERE %SQLUPPER(ExactName) %STARTSWITH %SQLUPPER('Ra')
For details on changing the collation defaults or using case transformation functions, see Collation.
More About
Range of Subscripts
When column is retrieved from a subscript, %STARTSWITH can be used as an index-limiting range condition, narrowing the range of column subscript values that needs to be traversed. The logic is to start the subscript range with the given substring prefix value, and stop as soon as the subscript value no longer starts with substring.
Other Equivalence Comparisons
%STARTSWITH performs an equivalence comparison on the initial characters of a string. You can perform other types of equivalence comparisons by using string comparison operators. These include the following:
-
An equivalence comparison on the entire string, using the equal sign operator:
SELECT Name,Home_State FROM Sample.Person WHERE Home_State = 'VT'
This example selects any record that contains the Home_State field value “VT”. Because Home_State is defined as SQLUPPER, this string comparison is not case-sensitive.
You can also perform a non-equivalence comparison on the entire string, using the not equal operator (<>).
-
An equivalence comparison of a substring to a value, using the Contains operator:
SELECT Name FROM Sample.Person WHERE Name [ 'y'
This example selects all Name records that contain the lowercase letter “y”. By default, a Contains operator comparison is case-sensitive, even when the field is defined as not case-sensitive.
-
A context-aware equivalence comparison using InterSystems SQL Search. One use of SQL Search is to determine if a value contains a specified word or phrase. SQL Search is not case-sensitive.
-
An equivalence comparison on the entire string to multiple values, using the IN keyword operator. For example, this statement selects any record that contains any of the specified Home_State field values.
SELECT Name,Home_State FROM Sample.Person WHERE Home_State IN ('VT','MA','NH','ME') ORDER BY Home_State
-
An equivalence comparison on the entire string to a value pattern, using the %PATTERN keyword operator:
SELECT Name,Home_State FROM Sample.Person WHERE Home_State %PATTERN '1U1"C"' ORDER BY Home_State
This example selects any record that contains a Home_State field value that matches the pattern of 1U (one uppercase letter) followed by 1"C" (one literal letter “C”). The Home_State abbreviations “NC” or “SC” fulfill this pattern.
-
An equivalence comparison of a substring with one or more wildcards to a value, using the LIKE keyword operator:
SELECT Name FROM Sample.Person WHERE Name LIKE '_a%'
This example selects all Name records that contain the letter “a” as the second letter. This string comparison uses the Name collation type to determine whether the comparison is case-sensitive or not.
For further details on these and other comparison conditional predicates, refer to the WHERE clause.
%SelectMode Setting
The %STARTSWITH predicate cannot use the current %SelectMode setting. A substring must be specified in Logical format, regardless of the %SelectMode setting. Specifying predicate value(s) in ODBC or Display format commonly results in no data matches or unintended data matches. This applies mainly to dates, times, and InterSystems IRIS format lists (%List).
In the following Dynamic SQL example, the %STARTSWITH predicate must specify the date substring in Logical format, not in %SelectMode=1 (ODBC) format. Rows with DOB Logical values beginning with 41 (dates from April 4 1953 ($HOROLOG 41000) through December 28 1955 ($HOROLOG 41999)) are selected:
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB %STARTSWITH '41%'"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
National Collation of Ambiguous Characters
In some national languages two characters or character combinations are considered first-pass collation equivalent. Commonly this is a character with or without an accent mark, such as in the Czech2 locale, in which CHAR(65) and CHAR(193) both collate as “A”. %STARTSWITH recognizes these characters as equivalent.
The following example shows the first-pass collation for Czech2 CHAR(65) (A) and CHAR(193) (Á):
M MA MÁ MAC MÁC MACX MÁCX MAD MÁD MB
When the query compiles, the national collation used at run time is unknown. Therefore, write %STARTSWITH subscript traversal code to satisfy the possible runtime scenarios.