|
|
Transact-SQL User's Guide |
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, varchar, and nvarchar datatypes and on datatypes that implicitly convert to char or varchar. A few string functions can also be used on binary and varbinary data. patindex can be used on text, char, nchar, varchar, and nvarchar columns.
You can concatenate binary and varbinary columns and char, nchar, varchar, and nvarchar columns. However, you cannot concatenate text 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 10-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.
Argument Type | Can Be Replaced By |
|---|---|
char_expr | A character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar 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, or nvarchar 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 tinyint, smallint or int), column name, variable, or constant expression. Maximum size ranges are noted, as they apply. |
start | An integer_expr. |
length | An integer_expr. |
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, 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 text value. For variable-length data in a table column, char_length strips the expression of trailing blanks before counting the number of characters. For multibyte character sets, the number of characters in the expression is usually less 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. |
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%", | 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 be used on text 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 4-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 | 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. |
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. |
upper | (char_expr) | Converts lowercase to uppercase. Returns a character value. |
The examples in this section use the following system functions:
The charindex and patindex functions return the starting position of a pattern you specify. Both take two arguments, but they work slightly differently, since patindex can use wildcard characters, but charindex cannot. charindex can be used only on char, nchar, varchar, nvarchar, binary, and varbinary columns; patindex works on char, nchar, varchar, nvarchar, and text columns.
Both functions take two arguments. The first is the pattern whose position you want. With patindex, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the preceding %) or last (omit the trailing %) characters in a column. For charindex, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.
To find the position at which the pattern "wonderful" begins in a certain row of the notes column of the titles table, using both functions, type this query:
select charindex("wonderful", notes),
patindex("%wonderful%",
notes)
from titles
where title_id = "TC3218" ------------- -------------
46 46
(1 row affected)
If you do not restrict the rows to be searched, the query returns all rows in the table and reports zero values for those rows that do not contain the pattern. In the following example, patindex finds all the rows in sysobjects that start with "sys" and whose fourth character is "a", "b", "c", or "d":
select name
from sysobjects
where patindex("sys[a-d]%", name) > 0
name
------------------------------
sysalternates
sysattributes
syscolumns
syscomments
sysconstraints
sysdepends
(6 rows affected)
The str function converts numbers to characters, with optional arguments for specifying the length of the number (including sign, decimal point, and digits to the right and left of the decimal point), and the number of places after the decimal point.
Set length and decimal arguments to str positive. The default length is 10. The default decimal is 0. The length should be long enough to accommodate the decimal point and the number's sign. The decimal portion of the result is rounded to fit within the specified length. If the integer portion of the number does not fit within the length, however, str returns a row of asterisks of the specified length.
For example:
select str(123.456, 2, 4)
--
**
(1 row affected)
A short approx_numeric is right-justified in the specified length, and a long approx_numeric is truncated to the specified number of decimal places.
The stuff function inserts a string into another string. It deletes a specified length of characters in expr1 at the start position. It then inserts expr2 string into expr1 string at the start position. If the start position or the length is negative, a NULL string is returned.
If the start position is longer than expr1, a NULL string is returned. If the length to delete is longer than expr1, it is deleted through the last character in expr1. For example:
select stuff("abc", 2,
3, "xyz") ----
axyz
(1 row affected)
To use stuff to delete a character, replace expr2 with NULL, not with empty quotation marks. Using " " to specify a null character replaces it with a space.
select stuff("abcdef", 2, 3,
null)---
aef
(1 row affected)
select stuff("abcdef", 2, 3,
"")----
a ef
(1 row affected)
The soundex function converts a character string to a 4-digit code for use in a comparison. It ignores vowels in the comparison. Nonalphabetic characters terminate the soundex evaluation. This function always returns some value. These two names have identical soundex codes:
select soundex ("smith"), soundex ("smythe")
----- -----
S530 S530
The difference function compares the soundex values of two strings and evaluates the similarity between them, returning a value from 0 to 4. A value of 4 is the best match. For example:
select
difference("smithers", "smothers") ---------
4
(1 row affected)
select difference("smothers", "brothers") ---------
2
(1 row affected)
Most of the remaining string functions are easy to use and to understand. For example:
Statement | Result |
|---|---|
select right("abcde", 3) | cde |
select right("abcde", 6) | abcde |
select right(0x12345000, 3) | 0x345000 |
select right(0x12345000, 6) | 0x12345000 |
select upper("torso") | TORSO |
select ascii("ABC") | 65 |
The following example uses the substring function. It displays the last name and first initial of each author, for example, "Bennet A".
select au_lname, substring(au_fname, 1, 1)
from authors
The substring function does what its name implies¾it returns a portion of a character or binary string.
The substring function always takes three arguments. The first can be a character or binary string, a column name, or a string-valued expression that includes a column name. The second argument specifies the position at which the substring should begin. The third specifies the length, in number of characters, of the string to be returned.
The syntax of the substring function looks like this:
substring(expression, start, length)
For example, here is how to specify the second, third, and fourth characters of the string constant "abcdef":
select x = substring("abcdef", 2, 3)x
---------
bcd
(1 row affected)
The following example shows how to extract the lower 4 digits from a binary field, where each position represents 2 binary digits:
select substring(xactid,5,2) from syslogs
You can concatenate binary or character expressions¾combine two or more character or binary strings, character or binary data, or a combination of them¾with the + string concatenation operator.
When you concatenate character strings, enclose each character expression in single or double quotes.
The concatenation syntax is:
select (expression + expression [+ expression]...)
Here is how to combine two character strings:
select ("abc" + "def") -------
abcdef
(1 row affected)
This query displays California authors' names under the column heading Moniker in last name-first name order, with a comma and space after the last name:
select Moniker = (au_lname + ", " + au_fname)
from authors
where state = "CA"
Moniker
-------------------------------------------------
White, Johnson
Green, Marjorie
Carson, Cheryl
O'Leary, Michael
Straight, Dick
Bennet, Abraham
Dull, Ann
Gringlesby, Burt
Locksley, Chastity
Yokomoto, Akiko
Stringer, Dirk
MacFeather, Stearns
Karsen, Livia
Hunter, Sheryl
McBadden, Heather
(15 rows affected)
To concatenate numeric or datetime datatypes, you must use the convert function:
select "The due date is " + convert(varchar(30),
pubdate)
from titles
where title_id = "BU1032"
---------------------------------------
The due date is Jun 12 1986 12:00AM
(1 row affected)
Adaptive Server evaluates the empty string ("" or '') as a single space. This statement:
select "abc" + "" + "def"
produces:
abc def
You can nest the string functions. For example, to display the last name and the first initial of each author, with a comma after the last name and a period after the first name, type:
select (au_lname + "," + " " + substring(au_fname, 1, 1) + ".")
from authors
where city = "Oakland"
--------------------------------------------
Green, M.
Straight, D.
Stringer, D.
MacFeather, S.
Karsen, L.
(5 rows affected)
To display the pub_id and the first 2 characters of each title_id for books priced more than $20, type:
select substring(pub_id + title_id, 1, 6)
from titles
where price > $20
--------------
1389PC
0877PS
0877TC
(3 rows affected)
|
|