String functions are used for various operations on binary data, character strings, or expressions, and they return values commonly needed for operations on character data. String function names are not keywords.
String functions have the following syntax:
SELECT function_name(parameters)
You can concatenate binary or character expressions, like this:
(expression expression [ expression]...)
When concatenating non-character, non-binary expressions, you must use the CONVERT datatype conversion function:
SELECT 'The price is ' + CONVERT(varchar(12), price)
FROM titles
WHERE price > 10.00
-------------------------
The price is 19.99
The price is 11.95
The price is 11.96
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 43.80
The price is 28.00
The price is 79.96
The price is 31.96
The price is 20.95
The price is 11.95
The price is 14.99
(15 row(s) affected)
Most string functions can be used only on char and varchar 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 (described later in this chapter) can be used on text, char, and varchar columns.
Concatenation can be used on binary and varbinary columns as well as on char and varchar columns.
String functions can be nested, and they can be used anywhere an expression is allowed. When you use constants with a string function, enclose them in single quotation marks.
For a complete list of string functions, see the Functions topic in the Microsoft SQL Server Transact-SQL Reference.
The following sections provide examples using string functions.