String Functions in T-SQL

String functions Perform operations on a string (char or varchar) input value and return a string or numeric value.

String functions let us extract various portions of character strings, change the case of strings, concatenate and reverse strings and perform many other types of manipulations.

String manipulations are an inherent part of any programming language. In transactional systems you might have to format the string accepted on a user-interface screen; in reporting situations you might need to concatenate or compare strings.

Transact-SQL includes numerous functions as shown below that are fairly simple to use.

String Functions:

1.ASCII

Returns the ASCII code value of the leftmost character of a character expression. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. 

Return type is integer.

Syntax: ASCII(Character_Expression)

Examples:

NOTE:  We can provide valid expression also to ASCII( ). i.e ascii(1+5) is valid.

2. CHAR( )

Converts an int ASCII code to a character.The integer expression range from 0 through 255. NULL is returned if the integer expression is not in this range

Syntax:               CHAR( integer_expression )

Examples:

3.LOWER( )

Returns a character expression after converting uppercase character data to lowercase.The character_expression is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Syntax:                           LOWER (character_expression)

Example:

4.UPPER()