String Functions

String functions perform various operations on character strings, expressions, and sometimes on binary data.

When you use constants with a string function, enclose them in single or double quotation marks. You can concatenate binary and character expressions, and nest them.

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, varchar, or 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.

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. SAP ASE automatically converts the argument to the desired type.

• Concatenating Expressions
  You can concatenate binary or character expressions; that is, you can combine two or more character or binary strings, character or binary data, or a combination of them with the + string concatenation operator. The maximum length of a concatenated string is 16384 bytes.
• Nest String Functions
  You can nest string functions. For example, use the substring function 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.
• Limits on String Functions
  Results of string functions are limited to 16KB. This limit is independent of the server’s page size. In Transact-SQL string functions and string variables, literals can be as large as 16K even on a 2KB page size.