Chapter 15: Using the Built-In Functions in Queries

String functions used for character strings or expressions

String functions are used for various operations on character strings or expressions. A few string functions can be used on binary data as well as on character data. You can also concatenate binary data or character strings or expressions.

String built-in functions return values commonly needed for operations on character data. String function names are not keywords.

The syntax for string functions takes the general form:

select function_name(arguments)

You can concatenate binary or character expressions like this:

select (expression + expression [+ expression]...)

When concatenating noncharacter, nonbinary expressions, you must use the convert function:

select "The price is " + convert(varchar(12),price)
from titles

Most string functions can be used only on char, nchar, unichar , varchar, univarchar, and nvarchar datatypes and on datatypes that implicitly convert to char, unichar, or varchar, univarchar. A few string functions can also be used on binary and varbinary data. patindex can be used on text, unitext, char, nchar, unichar, varchar, nvarchar, and univarchar columns.

You can concatenate binary and varbinary columns and char, unichar, nchar, varchar, univarchar, and nvarchar columns. If you concatenate unichar and univarchar with char, nchar, nvarchar, and varchar, the result is unichar or univarchar. However, you cannot concatenate text, unitext, or image columns.

You can nest string functions and use them anywhere an expression is allowed. When you use constants with a string function, enclose them in single or double quotes.

Each function also accepts arguments that can be implicitly converted to the specified type. For example, functions that accept approximate numeric expressions also accept integer expressions. Adaptive Server automatically converts the argument to the desired type.

Table 15-2 lists the arguments used in string functions. If a function takes more than one expression of the same type, the arguments are numbered char_expr1, char_expr2, and so on.

**Table 15-2: Arguments used in string functions**
Argument type	Can be replaced by
char_expr	A character-type column name, variable, or constant expression of char, unichar, varchar, univarchar, nchar, or nvarchar type. Functions that accept text column names are noted in the explanation. Constant expressions must be enclosed in quotation marks.
uchar_expr	A character-type column name, variable, or constant expression of unichar or univarchar type. Functions that accept text column names are noted in the explanation. Constant expressions must be enclosed in quotation marks.
expression	A binary or character column name, variable or constant expression. Can be char, varchar, nchar,, nvarchar, unichar or univarchar data, as for char_expr, plus binary or varbinary.
pattern	A character expression of char, nchar, varchar, or nvarchar datatype that may include any of the pattern-matching wildcards supported by Adaptive Server.
approx_numeric	Any approximate numeric (float, real, or double precision) column name, variable, or constant expression.
integer_expr	Any integer (such as bigint, tinyint, smallint or int, unsigned bigint, unsigned int or unsigned smallint), column name, variable, or constant expression. Maximum size ranges are noted, as they apply.
start	An integer_expr.
length	An integer_expr.

Table 15-3 lists function names, arguments, and results.

**Table 15-3: String functions, arguments, and results**
Function	Argument	Result
ascii	(char_expr)	Returns the ASCII code for the first character in the expression.
char	(integer_expr)	Converts a single-byte integer value to a character value. char is usually used as the inverse of ascii. integer_expr must be between 0 and 255. Returns a char datatype. If the resulting value is the first byte of a multibyte character, the character may be undefined.
charindex	(expression1, expression2)	Searches expression2 for the first occurrence of expression1 and returns an integer representing its starting position. If expression1 is not found, it returns 0. If expression1 contains wildcard characters, charindex treats them as literals.
char_length	(char_expr)	Returns an integer representing the number of characters in a character expression, or a text or unitext value. For variable-length data in a table column, char_length returns the number of characters. For fixed-length data, it returns the defined length of the column. For multibyte character sets, the number of characters in the expression is usually fewer than the number of bytes; use the system function datalength to determine the number of bytes.
difference	(char_expr1, char_expr2)	Returns an integer representing the difference between two soundex values. See soundex, below.
n	(character_expression, integer_expression)	Returns a specified number of characters on the left end of a character string.
len	(string_expression)	Returns the number of characters, not the number of bytes, of a specified string expression, excluding trailing blanks.
lower	(char_expr)	Converts uppercase to lowercase. Returns a character value.
ltrim	(char_expr)	Removes leading blanks from the character expression. Only values equivalent to the space character in the SQL special character specification are removed.
patindex	(“%pattern%”, char_expr [using {bytes \| chars \| characters}])	Returns an integer representing the starting position of the first occurrence of pattern in the specified character expression; returns 0 if pattern is not found. By default, patindex returns the offset in characters. To return the offset in bytes, that is, multibyte character strings, specify using bytes. The % wildcard character must precede and follow pattern, except when searching for first or last characters. See “Character strings in query results” for a description of the wildcard characters that can be used in pattern. patindex can be used on text and unitext data.
replicate	(char_expr, integer_expr)	Returns a string with the same datatype as char_expr, containing the same expression repeated the specified number of times or as many times as will fit into a 255-byte space, whichever is less.
reverse	(expression)	Returns the reverse of the character or binary expression; if expression is “abcd”, it returns “dcba”; if expression is 0x12345000, returns 0x00503412.
right	(expression, integer_expr)	Returns the part of the character or binary expression starting at the specified number of characters from the right. Return value has the same datatype as the character expression.
rtrim	(char_expr)	Removes trailing blanks. Only values equivalent to the space character in the SQL special character definition are removed.
soundex	(char_expr)	Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte Roman letters.
space	(integer_expr)	Returns a string with the indicated number of single-byte spaces.
str	(approx_numeric [, length [, decimal] ])	Returns a character representation of the floating point number. length sets the number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks); decimal sets the number of decimal digits to be returned. length and decimal are optional. If given, they must be nonnegative. Default length is 10; default decimal is 0. str rounds the decimal portion of the number so that the results fit within the specified length.
str_replace	("string_expression1", "string_expression2", "string_expression3")	Replaces any instances of the second string expression (string_expression2) that occur within the first string expression (string_expression1) with a third expression (string_expression3).
stuff	(char_expr1, start, length, char_expr2)	Delete length characters from char_expr1 at start, and then insert char_expr2 into char_expr1 at start. To delete characters without inserting other characters, char_expr2 should be NULL, not ” ”, which indicates a single space.
substring	(expression, start, length)	Returns part of a character or binary string. start specifies the character position at which the substring begins. length specifies the number of characters in the substring.
to_unichar	(integer_expr)	Returns a unichar expression having the value of the integer expression. If the integer expression is in the range 0xD800..0xDFFF, a sqlstate exception is raised, an error is printed, and the operation is aborted. If the integer expression is in the range 0..0xFFFF, a single Unicode value is returned. If the integer expression is in the range 0x10000..0x10FFFF, a surrogate pair is returned.
upper	(char_expr)	Converts lowercase to uppercase. Returns a character value.
uhighsurr	(uchar_expr, start )	Returns 1 if the Unicode value at start is the high half of a surrogate pair (which should appear first in the pair). Otherwise, returns 0. This function allows you to write explicit code for surrogate handling. Particularly, if a substring starts on a Unicode character where uhighsurr() is true, extract a substring of at least 2 Unicode values, as substr() does not extract just 1. substr() does not extract half of a surrogate pair).
ulowsurr	(uchar_expr, start)	Returns 1 if the Unicode value at start is the low half of a surrogate pair (which should appear second in the pair). Otherwise, returns 0. This function allows you to explicitly code around the adjustments performed by substr(), stuff(), and right(). Particularly, if a substring ends on a Unicode value where ulowsurr() is true, extract a substring of 1 less characters (or 1 more), since substr() does not extract a string that contains an unmatched surrogate pair.
uscalar	(uchar_expr)	Returns the Unicode scalar value for the first Unicode character in an expression. If the first character is not the high-order half of a surrogate pair, then the value is in the range 0..0xFFFF. If the first character is the high-order half of a surrogate pair, a second value must be a low-order half, and the return value is in the range 0x10000..0x10FFFF. If this function is called on a uchar_expr containing an unmatched surrogate half, a SQLSTATE exception is raised, an error printed, and the operation aborted.

View this book as PDF