Chittibabu

String functions let you extract various portions of character strings, change the case of strings, concatenate and reverse strings and perform many other types of manipulations. All built-in string functions are deterministic with the exception of CHARINDEX and PATINDEX.
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 that are fairly simple to use.

Contents [hide] 1 LEFT Function 2 RIGHT Function 3 LTRIM and RTRIM Functions 4 SUBSTRING Function 5 REVERSE Function 6 CHARINDEX and PATINDEX Function 7 REPLACE Function 8 STUFF Function 9 LEN Function 10 REPLICATE Function 11 SPACE Function 12 UPPER and LOWER Functions 13 ASCII Function 14 UNICODE Function 15 CHAR Function 16 NCHAR Function 17 QUOTENAME Function 18 STR Function 19 SOUNDEX and DIFFERENCE Functions 20 More SQL Server Functions

LEFT Function

Transact-SQL supports retrieving portions of the string. For instance, to retrieve the first few characters from the left of the string you use the LEFT function. The following example retrieves first four letters of employee last names in the AdventureWorksDW database:
Results (abbreviated):

RIGHT Function

The RIGHT function retrieves the portion of the string counting from the right. For example:
Results:

LTRIM and RTRIM Functions

Notice that RIGHT and LEFT functions don't check for blank characters. In other words if your string contains a couple of leading blanks then LEFT(string_variable, 2) will return you two blank spaces, which might not be exactly what you want. If your data needs to be left aligned you can use LTRIM function, which removes the leading blanks. Similarly the RTRIM function removes the trailing characters. For instance, the following UPDATE statement will left align (remove any number of leading blanks) last names:
Similarly, if your data is padded with spaces and you don't wish to see spaces in your output you can use the RTRIM function. You could combine the two functions to remove both leading and trailing spaces as follows:

SUBSTRING Function

SUBSTRING function retrieves a portion of the string starting at the specified character and bringing back the number of characters specified; the syntax is:
The following example will retrieve four characters from the employee last names, starting at the third character:
Results:
Notice that the SUBSTRING function finds the starting character by counting from the left. In other words, if we run SUBSTRING(LastName, 3, 4) against the last name of "Buchanan" we start on the 3rd character from the left - "c".

REVERSE Function

The REVERSE function gives you a mirror image of a given string. The following example returns the mirror image of employee last names:
Results:

CHARINDEX and PATINDEX Function

Transact-SQL supports two functions for finding an occurrence of a particular character (or number of characters) within a string: CHARINDEX and PATINDEX. CHARINDEX finds the starting position of a single character or string within a given column (or variable). In addition, if you suspect that the value you are searching for might occur multiple times within a given string you can specify the character number at which you want to start searching. The syntax of the function is:
For example, you might wish to find a position of an apostrophe inside employee last names. The following query shows how this can be achieved using CHARINDEX function:
Results:
Perhaps a more interesting example is finding an occurrence of a string, as opposed to an occurrence of a character. For example, city names often end with "ville", as in Huntsville or Clarksville. The following query finds the starting position of "ville" within city names:
Results:
The next example finds the occurrence of the value within a variable, starting search at the 20th character:
Results:
PATINDEX function is very similar to CHARINDEX - it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX; this function searches for a pattern. If you use a % wildcard with CHARINDEX you won't find anything, unless your data contains percent signs. If you're searching for a pattern at the end of the string expression, then you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string). The following query returns the same results as CHARINDEX example:

REPLACE Function

REPLACE function replaces some characters within a string with another set of characters. The syntax is:
For example, the following query replaces each occurrence of the word "payable" with "receivable":

STUFF Function

The STUFF function inserts a set of characters into a given string at a given position. The syntax is:
For example, the following query adds " town " string to every city in DimGeography table:
Results:
You saw how to find the position of a specific character or number of characters using CHARINDEX. Now you can apply that knowledge and use STUFF function to replace characters based on their position.
The following example determines the position of 'ville' in the City column and then replaces it with 'town':
Results:

LEN Function

The LEN function finds the length of the character string. The function takes the string as a single argument. For example, the following query shows the length of each city name:
Results:

• Note: use the DATALENGTH system function to determine the number of characters in a TEXT column

REPLICATE Function

The REPLICATE function repeats a given string specified number of times. The syntax is: REPLICATE(string, number of times). For example, the following query prints the string '100' five times:
Result:
One common usage of REPLICATE function is to combine it with other string functions and replace leading or trailing spaces with another character.
Result:

SPACE Function

The SPACE function is an equivalent of using REPLICATE to repeat spaces. This function takes a single argument - number of spaces you want to print.

UPPER and LOWER Functions

UPPER and LOWER functions change the case of the query's output. Both functions accept a string expression as the only argument. For example, the following query will return the US cities and corresponding states in mixed case:
Results (abbreviated):

ASCII Function

ASCII function returns the ASCII code value of the leftmost character of a string. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. Upper case and lower case letters translate into different ASCII values, as the following example shows:
Results:

UNICODE Function

UNICODE function works just like ASCII, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.

CHAR Function

The CHAR function does the opposite of ASCII - it returns an alphanumeric equivalent of an ASCII code. CHAR function accepts a single argument - a number between 0 and 255. It is often necessary to append a carriage return, line feed, or both to the query output. In such cases you can effectively use CHAR function, as follows:
Results:

NCHAR Function

NCHAR function works exactly like CHAR except it returns the Unicode character. This function is useful if you're working with large international character sets. Unlike CHAR function NCHAR can handle values between 0 and 65535.

QUOTENAME Function

The QUOTENAME function appends square brackets to the beginning and end of the string expression and thereby makes a string expression a valid SQL Server identifier. QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally it's a bad idea to use reserved words, special characters and spaces inside your object names. However at times, such as when working with 3rd party software, you do not have a choice. The following example uses QUOTENAME function to create a valid identifier:
Results:

STR Function

The STR function converts a numeric value into a string. This function can be considered as a special case of CAST or CONVERT functions, both of which let you convert the variable from one data type into another compatible datatype. The STR function allows specifying the length of the string variable returned, as well as how many decimal points to include in the output. The syntax is:
The length argument specifies the total length of the string. For example, '2.4503' consists of 6 characters. The decimal argument specifies how many characters are allowed to the right of the decimal point. For example, the following query returns a rounded currency rate as a string; the string has a total of six characters, five of which could be to the right of the decimal point:
Results:

SOUNDEX and DIFFERENCE Functions

The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar and very seldom used. SOUNDEX provides a four character representation of the string (SOUNDEX code) and is supposed to help you determine whether two strings sound alike. For example, the following query retrieves SOUNDEX values for a few employees:
Results:


The DIFFERENCE function provides a degree of similarity (or lack thereof) between the two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function then the degree of similarity is the highest - 4. Otherwise, the DIFFERENCE function will return 3, 2, 1 or 0. The DIFFERENCE function could be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:
Results: