List of Built-in String Functions in SQL Server

Please visit my new Web Site https://coderstechzone.com

String manipulation is one of the most important task for each developer of each language. Its not possible to remember all string list cause as a human we have few limitations. Everytime i google different type of built in functions for different languages. If i can write a complete list of string functions with its corrsponding syntax (parameter list with description) then it will help me as well as readers to easily identify the function which will surely accelarate our productivity.

Lets we need to extract various part of a string body, cut a specific portion means substring, need to change upper to lower or lower to upper, replace, stuff or padding, start & end index of a charater set to match then we can easily depend on SQL SERVER built in function list. All built-in string functions are deterministic with the exception of CHARINDEX.

Focus Area:

1. ASCII	9. NCHAR	17. SPACE
2. CHAR	10. REPLACE	18. STR
3. CHARINDEX	11. QUOTENAME	19. STUFF
4. DIFFERENCE	12. REPLICATE	20. SUBSTRING
5. LEFT	13. REVERSE	21. UNICODE
6. LEN	14. RIGHT	22. UPPER
7. LOWER	15. RTRIM
8. LTRIM	16. SOUNDEX

Use ASCII:
The ASCII() function returns the ASCII code of the first left charater from your given charater or string as the argument.

Signature:
ASCII ( character_expression )
Where character_expression can take a single charater or a set of charater means string or varchar.

Example:

SELECT ASCII('A')
-- Ans. is 65
SELECT ASCII('ASP.NET C#/VB.NET')
-- Ans. also 65

So from example we found conclusion that ASCII() function consider only the first charater from provided string whatever next.

Use CHAR():
The CHAR() function is the opposite of ASCII() function. ASCII() function returns the ASCII code where as CHAR() function convert the ASCII code to its alphanumeric code.

Signature:
CHAR ( integer_expression )
Where integer_expression can take only int value ranges 0 to 255. If the value does not satisfy the range then you will get NULL.

Example:

SELECT CHAR(65)
--Ans. is A

The another important use of CHAR() function is you can add dynamically carriage return, line feed or the query output. Lets i want to display category and category wise list in a new line then the query should be:

PRINT 'ASP.NET Blog List:'+CHAR(13)+'http://shawpnendu.blogspot.com'
-- RETURN
-- ASP.NET Blog List:
-- http://shawpnendu.blogspot.com/

Control charater TAB=CHAR(9), Line Feed=CHAR(10) & Carriage Return=CHAR(13).

Use CHARINDEX():
Returns the starting position of a character string. CHARINDEX() function is widely used to findout a sequence of string or the sequence occurence.

Signature:
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Where expression1 is for your sequence or a charater that you are looking for. The expression2 is the column name of your database where you want to search. And the third one is optional. Here you can notify the SQL Server from which location the server starts searching to find your sequence from your provided column. If you does not specify the location then the search starts from beginning of the search column.

Example:

-- Let we have the following records:
-- IMSI=470010210020903;
-- MSISDN=8801723500902;
-- CURRENTNAM=BOTH;
-- CAT=COMMON;
-- ODBROAM=ODBOH;
-- ODBPLMN=PLMN1;
-- In the BatchData column of HLR table
-- Our query is to find out ALL MSISDN Containg rows

SELECT BatchData FROM HLR WHERE CHARINDEX('MSISDN',BatchData)>-1

-- Output will be MSISDN=8801723500902

Now we have change our requirement. We need to select all MSISDN. So the query should be:

SELECT SUBSTRING(BatchData,CHARINDEX('=',BatchData)+1,
LEN(BatchData)-CHARINDEX('=',BatchData)) [MSISDN] FROM HLR
WHERE CHARINDEX('MSISDN',BatchData)>-1

-- Output is: 8801723500902

About SUBSTRING() function i will describe later in this post.

Use DIFFERENCE():
Returns the difference between two character expressions or string based on sound recognition logic. DIFFERENCE() function return 0-4 based on matching. Where 0 means not matched & 4 means strongly matched. 3 means less sronger than 4.

Signature:
DIFFERENCE ( character_expression , character_expression )

Example:

SELECT DIFFERENCE('asp','asp')
-- Will return 4

SELECT DIFFERENCE('asp','php')
-- Will return 2

Use LEFT():
Transact-SQL supports retrieving a portion of a database column or string. For instance, to retrieve or cut the first few characters from the left of the string you can use the LEFT() built in function.

Signature:
LEFT ( character_expression , integer_expression )
First character_expression is your column name or a string & integer_expression is the number that how many left charaters you want.

Example:

SELECT LEFT('.Net Mixer',4)
-- Will return .Net

SELECT LEFT('.Net Mixer',100)
-- Will return .Net Mixer
-- Means if you provide more than total length
-- Then SQL Server returns Full String

SELECT LEFT('.Net Mixer',0)
-- Will return Blank

SELECT LEFT('.Net Mixer',-1)
-- Will return the error
-- 'Invalid length parameter passed to the left function.'
-- Negetive value will return error.

Use RIGHT():
Same as LEFT() function except reurns the right portion instead of left.

Signature:
RIGHT ( character_expression , integer_expression )
First character_expression is your column name or a string & integer_expression is the number that how many right charaters you want.

Example:

SELECT RIGHT('.Net Mixer',4)
-- Will return ixer

SELECT RIGHT('.Net Mixer',100)
-- Will return .Net Mixer
-- Means if you provide more than total length
-- Then SQL Server returns Full String

SELECT RIGHT('.Net Mixer',0)
-- Will return Blank

SELECT RIGHT('.Net Mixer',-1)
-- Will return the error
-- 'Invalid length parameter passed to the right function.'
-- Negetive value will return error.

Use LEN():
The LEN() function is a very useful function. It returns the number of characters in a string.

Signature:
LEN ( string_expression )
The string_expression is either your database column name or a string.

Example:

SELECT LEN('.Net Mixer')
-- Will return 10

Use LOWER() & UPPER():
Convert the database column value or a string to lowercase or small letter. And UPPER() Function will convert to Capital Letter

Signature:
LOWER ( character_expression ) OR UPPER ( character_expression )
The character_expression may be your databse column or a fixed string.

Example:

SELECT LOWER('.Net Mixer')
-- Will return .net mixer

SELECT UPPER('.Net Mixer')
-- Will return .NET MIXER

Another example is let you have a reuirement like show all article title where the first charater will be the capital letter or upper case & rest of all are small letter or lower case. Then the query should be:

SELECT UPPER(LEFT(Title,1))+LOWER(RIGHT(Title,LEN(Title)-1)) FROM Articles
-- Dtepart, dateadd, datediff sql server standard datetime function
-- For each category or group select first n rows, top n rows, last n rows, bottom n rows, random data

Use LTRIM() & RTRIM():
You can use LTRIM() function to remove all leading blank charater from your column & can remove all trailing blank spaces by RTRIM() SQL Server built in function. One thing keep in mind that the above specified LEFT() & RIGHT() function never trim data. Hope now you can understand the difference between LTRIM() & LEFT() as well as RTRIM() & RIGHT().

Signature:
LTRIM ( character_expression )
RTRIM ( character_expression )
From the above syntax character_expression either a column name or a string

Example:

SELECT LTRIM(' .Net Mixer')
-- Will return .Net Mixer

SELECT RTRIM(' .Net Mixer ')
-- Will return .Net Mixer

SELECT LTRIM(RTRIM(' .Net Mixer '))
-- Will return .Net Mixer

Use NCHAR() & UNICODE():
The NCHAR() function exactly works like CHAR() function except return type. NCHAR() function return Unicode character. The another difference is in value range where NCHAR() argument range is 0 to 65535. You find this function is useful if you're working with large international character sets.

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

Signature:
NCHAR ( integer_expression )
From the above syntax integer_expression can take only positive integer from 0 through 65535. If a value outside this range is specified, NULL is returned.

UNICODE ( 'ncharacter_expression' )
From the above syntax 'ncharacter_expression' is an nchar or nvarchar expression. One thing note that UNICODE() function always consider the first charater if you supplied a string.

Example:

DECLARE @nstring nchar(8)
SET @nstring = N'København'
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)),
NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1)))

-- Output is 248,ø respectively

Use REPLACE():
The REPLACE() function replaces some characters or sub string within a string with another string or set of characters.

Signature:
REPLACE (string_expression1 , string_expression2 , string_expression3 )
Where expression1 is the column name or a string where you want to replace something. Expression2 is the sub string or a set of character you want to replace from expression1. And finally expression3 is the replacing value.

Example:

SELECT REPLACE(Title, 'Signature', 'Syntax') AS Title
FROM Articles WHERE Approved=1

-- Table has: DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function Signature
-- The output: DATEPART, DATEADD, DATEDIFF SQL Server Standard Datetime Function Syntax
-- Means Signature replaced by Syntax word.

Use QUOTENAME():
The QUOTENAME() functions is used to add square bracket or third bracket to the provided column or a string. It will be helpful when you want to create a dynamic query. Because you knew that we used square quote for thos columns which contains spaces or keyword. Let you have to work with a partner where they put all table names & corresponding column into a table. Your task is to create SQL for insert/delete/select. At this stage the QUOTENAME will work fine.

Signature:
QUOTENAME(string_expression)
Where string_expression is a column name or a string.

Example:

SELECT QUOTENAME(tblName) FROM tblList

--Input: student list
--Output: [student list]

Use REPLICATE():
The REPLICATE() function repeats a given string according to a specified occurance.

Signature:
REPLICATE(string_expression, integer_expression)
Where string_expression is your column name or a string & integer_expression is the number of occurance required.

Example:

SELECT REPLICATE('.Net Mixer',3)

--Output: .Net Mixer.Net Mixer.Net Mixer

Use REVERSE():
The REVERSE() function returns you reverse output or a mirror output for a given column or a string.

Signature:
REVERSE ( character_expression )
Where character_expression may be a column or a set of charater or string.

Example:

SELECT REVERSE('Asp.Net')

--Output: teN.psA

Use SOUNDEX():
The SOUNDEX() function returns a fixed 4 charater alphanumeric code.

Signature:
SOUNDEX ( character_expression )
Where character_expression will be a column or string.

Example:

SELECT SOUNDEX('Asp.Net'),SOUNDEX('Asp.Net')
--Output: A210,A210 means both are similar

SELECT SOUNDEX('ASP'),SOUNDEX('AJAX')
--Output: A210,A220

See also DIFFERENCE() function described above.

Use SPACE():
The SPACE() function is almost similar to REPLICATE() function except here you can use space only instead of a substring.

Signature:
SPACE ( integer_expression )
Where integer_expression is any positive number.

Example:

SELECT 'Asp.Net'+SPACE(0)+'Tutorial'
--Output: Asp.NetTutorial

SELECT 'Asp.Net'+SPACE(1)+'Tutorial'
--Output: Asp.Net Tutorial

Use STR():
The STR() function just convert a numeric value to string (Round up works ). You can specify the decimal length as well as total length thats why this is always imprtant importatnt to us for reporting purposes.

Signature:
STR ( float_expression [ , length [ , decimal ] ] )
Where first argument takes a numeric value, 2nd argument is the total length & third one is the decimal places.

Example:

--Let you have few sample data
SELECT STR(1234.456789, 5, 0)
--: 1234 Pad one space at first

SELECT STR(1234.456789, 7, 2)
--:1234.46 (Round-up)

SELECT STR(1234.456789, 6, 2)
--:1234.5 (Since length was 6)

SELECT STR(1234.456789, 3, 2)
--:*** (Since 3 is the total length where as integer value length=4)

So note that
1. If the provided number is less than the specified length then leading spaces will be added.
2. If the result is greater than the specified length then fractional part will be truncated with roud-up
3. If provided length is less than integer part value you will get number of asterisk equal to provided length.

Use STUFF():
The STUFF() function delete or remove a specified length & add or insert or stuff a string into that position.

Signature:
STUFF ( character_expression , start , length , character_expression )
Where character_expression is a column or a string, start means from which position you want to delete on the other hand stuff, length means how many character you want to remove from start position & character_expression is a set of charater or a charater that you want to stuff.

Example:

SELECT STUFF('ASP.NET',4,1,'dot')

-- Here start position 4 means start from .
-- Length=1 means remove one charater from osition 4
-- 'dot' means stuff dot in to the position 4 whatever its length

Use SUBSTRING():
The most popular function. The SUBSTRING() built in function extract or retrieves or return or cut a portion of the column/string.

Signature:
SUBSTRING ( expression , start , length )
Where expression is column or a string. Start means start position from where you want to cut & length means how many charaters you want to cut.

Example:

SELECT SUBSTRING('Asp.Net',4,4)
--Output: .Net

SELECT SUBSTRING('Asp.Net',4,100)
--Output: Same as above

SELECT SUBSTRING('Asp.Net',-100,2)
--Output is blank

SELECT SUBSTRING('Asp.Net',100,-2)
--Output: ERROR: Invalid length parameter passed to the substring function

Ref:
http://msdn.microsoft.com/en-us/library/aa258891(SQL.80).aspx