Tuesday, February 1, 2011

Built-in Functions - String Functions

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.



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:
1.SELECT LEFT(LastName, 4) AS FirstFourLettersOfLastName,  LastName FROM dbo.DimEmployee
Results (abbreviated):

RIGHT Function

The RIGHT function retrieves the portion of the string counting from the right. For example:
1.SELECT RIGHT(LastName, 4) AS FirstFourLettersOfLastName,  LastName as FullLastName FROM dbo.DimEmployee
1.LastFourLettersOfLastName FullLastName 
2.bert Gilbert 
3.rown Brown 
4.ello Tamburello 
5.ters Walters 
6.ters Walters

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:
1.UPDATE DimEmployee SET LastName = LTRIM(LastName)
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:
1.UPDATE DimEmployee SET LastName = LTRIM(RTRIM(LastName))


SUBSTRING function retrieves a portion of the string starting at the specified character and bringing back the number of characters specified; the syntax is:
1.SUBSTRING(string, starting_character_number, number_of_characters_to_return)
The following example will retrieve four characters from the employee last names, starting at the third character:
1.SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM DimEmployee
1.PortionOfLastName FullLastName 
2.lber Gilbert 
3.mbur Tamburello 
4.lter Walters 
5.lter Walters
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:
1.SELECT  REVERSE(LastName) AS MirrorImage,  LastName AS FullLastName  FROM DimEmployee
1.MirrorImage FullLastName 
2.trebliG Gilbert 
3.nworB Brown 
4.ollerubmaT Tamburello 
5.sretlaW Walters 
6.sretlaW Walters


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:
1.CHARINDEX(search value, string, starting search location)
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:
1.SELECT  CHARINDEX('''', LastName) AS ApostrophePosition,  LastName AS FullLastName  
2.FROM DimEmployee  WHERE lastname LIKE '%''%'
1.ApostrophePosition FullLastName 
2.2 D'Hers 
3.2 D'sa
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:
1.SELECT  CHARINDEX('ville', city) AS Position,  City    FROM dimGeography  WHERE city LIKE '%ville'
1.Position City 
2.5 Daleville 
3.10 Campbellsville 
4.4 Melville 
5.6 Crossville 
6.5 Maryville
The next example finds the occurrence of the value within a variable, starting search at the 20th character:
1.DECLARE @variable VARCHAR(255) 
2.SELECT @variable = 'this is a string. this is also a string' 
3.SELECT  CHARINDEX('string', @variable, 20) AS Position
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:
1.SELECT  PATINDEX('%ville%', city) AS Position,  City FROM dimGeography  WHERE city LIKE '%ville%'

REPLACE Function

REPLACE function replaces some characters within a string with another set of characters. The syntax is:
1.REPLACE(string expression, value to be replaced, replacing value)
For example, the following query replaces each occurrence of the word "payable" with "receivable":
1.SELECT AccountDescription,  REPLACE(AccountDescription, 'payable', 'receivable') AS DreamOn  
2.FROM dimAccount  WHERE AccountDescription LIKE '%payable%'

STUFF Function

The STUFF function inserts a set of characters into a given string at a given position. The syntax is:
1.STUFF(string to manipulate, starting position, length, characters to insert)
For example, the following query adds " town " string to every city in DimGeography table:
1.SELECT  STUFF(city, 5, 6, ' town ') AS Manipulated,  City  FROM dimGeography
1.Manipulated City 
2.Rock town n Rockhampton 
3.Town town  Townsville 
4.Clov town  Cloverdale 
5.Find town  Findon 
6.Pert town  Perth
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':
1.SELECT STUFF(city, CHARINDEX('ville', city), 6, ' town ') AS Manipulated,  City
2.FROM dimGeography  WHERE city LIKE '%ville'
1.Manipulated City 
2.Campbells town  Campbellsville 
3.Mel town  Melville 
4.Cross town  Crossville 
5.Mary town  Maryville 
6.Nash town  Nashville

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:
1.SELECT  LEN(city) AS number_of_characters,  City  FROM dimGeography
1.Number_of_characters City 
2.10 Alexandria 
3.13 Coffs Harbour 
4.12 Darlinghurst 
5.8 Goulburn 
6.9 Lane Cove
  • Note: use the DATALENGTH system function to determine the number of characters in a TEXT column


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:
One common usage of REPLICATE function is to combine it with other string functions and replace leading or trailing spaces with another character.
1.DECLARE @StringWithLeadingSpaces VARCHAR(10)   
2.SELECT @StringWithLeadingSpaces= '     SD3L6AA'   
3.SELECT @StringWithLeadingSpaces =  REPLICATE('0', LEN(@StringWithLeadingSpaces)
4.  - LEN(LTRIM(@StringWithLeadingSpaces)))  + LTRIM(@StringWithLeadingSpaces) 
5.SELECT @StringWithLeadingSpaces AS StringWithOUTLeadingSpaces

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:
1.SELECT UPPER(LEFT(City, 1)) +  LOWER(SUBSTRING(City, 2, (LEN(City) - 1))) + ',' + SPACE(2)
2.  + UPPER(LEFT(StateProvinceName, 1)) +  LOWER(SUBSTRING(StateProvinceName, 2,
3. (LEN(StateProvinceName) - 1)))  AS CityAndState
4.  FROM DimGeography  WHERE CountryRegionCode = 'us'
Results (abbreviated):

2.Chandler,  Arizona 
3.Gilbert,  Arizona 
4.Mesa,  Arizona 
5.Phoenix,  Arizona 
6.Scottsdale,  Arizona

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:
1.SELECT ASCII('A') AS UpperCase, ASCII('a') AS LowerCase
1.UpperCase   LowerCase 
2.----------- ----------- 
3.65          97

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:
1.SELECT   'My Output'   + --Add three carriage returns and a line feed:
2.   REPLICATE(CHAR(10), 3) + CHAR(13)  + 'AnotherOutput'
2.My Output       

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.


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:
1.SELECT QUOTENAME('column name with spaces')
1.[column name with spaces]

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:
1.STR(numeric value, length, decimal)
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:
1.SELECT  EndOfDayRate,  STR(EndOfDayRate, 6, 4) AS string_value
2.  FROM factCurrencyRate  WHERE CurrencyKey = 3 AND timeKey = 2
1.EndOfDayRate string_value 
2.1.000900811 1.0009


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:
1.SELECT  SOUNDEX(LastName) AS soundex_code,  LastName  FROM DimEmployee

1.soundex_code string_value 
2.S650 Sharma 
3.S100 Shoop 
4.S150 Spoon 
5.S520 Song 
6.S520 Singh 
7.S550 Simon 
8.S530 Smith
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:
1.SELECT LastName FROM DimEmployee WHERE DIFFERENCE (LastName, 'que') > 2


Post a Comment


Web Design Company karimnagar, Web Designing warangal, Logo Design Company nizamabad, Indian Website Design Company,