Professional SQL Server™ 2005 Programming

A.9. String Functions

The string functions perform actions on string values and return strings or numeric values. The string functions are:

• ASCII

• CHAR

• CHARINDEX

• DIFFERENCE

• LEFT

• LEN

• LOWER

• LTRIM

• NCHAR

• PATINDEX

• QUOTENAME

• REPLACE

• REPLICATE

• REVERSE

• RIGHT

• RTRIM

• SOUNDEX

• SPACE

• STR

• STUFF

• SUBSTRING

• UNICODE

• UPPER

A.9.1. ASCII

The ASCII function returns the ASCII code value of the leftmost character in character_expression. The syntax is as follows:

ASCII(<character_expression>)

A.9.2. CHAR

The CHAR function converts an ASCII code (specified in expression) into a string. The syntax is as follows:

CHAR(<expression>)

The expression can be any integer between 0 and 255.

A.9.3. CHARINDEX

The CHARINDEX function returns the starting position of an expression in a character_string. The syntax is as follows:

CHARINDEX(<expression>, <character _ string> [, <start_ location>])

The expression parameter is the string, which is to be found. The character_string is the string to be searched, usually a column. The start_location is the character position to begin the search, if this is anything other than a positive number, the search will begin at the start of character_string.

A.9.4. DIFFERENCE

The DIFFERENCE function returns the difference between the SOUNDEX values of two expressions as an integer. The syntax is as follows:

DIFFERENCE(<expression1>, <expression2>)

This function returns an integer value between 0 and 4. If the two expressions sound identical (for example, blue and blew) a value of 4 will be returned. ...