Sunday 6 November 2011

String Functions in SQL


String Functions in SQL

In SQL Server there are several built-in string functions to perform string manipulations a mainly used to change the case of strings, concatenate strings, reverse strings, extract various part of strings. All below functions takes string input value and return a string or numeric value.
ASCII : Returns the ASCII code value for a character
Syntax: ASCII(character)
SELECT ASCII('a') returns 97
SELECT ASCII('A') returns 65


For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57
UNICODE : UNICODE function works just like ASCII function, except returns Unicode standard integer value. UNICODE could be useful if you are working with international character sets.
Syntax: UNICODE(character)
SELECT UNICODE('F') returns 70
SELECT UNICODE('STRING FUNCTION') returns 83 (leftmost character of string)
LOWER : Convert character strings data into lowercase.
Syntax: LOWER(string)
SELECT LOWER('STRING FUNCTION') returns string function
UPPER : Convert character strings data into Uppercase.
Syntax: UPPER(string)
SELECT UPPER('string function') returns STRING FUNCTION
LEN : Returns the length of the character string.
Syntax: LEN(string)
SELECT LEN('STRING FUNCTION')returns15
REPLACE : Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).
Syntax: REPLACE('string1','string2','string3')
SELECT REPLACE('STRING FUNCTION','STRING','SQL') returns SQL Function
Returns NULL if any one of the arguments is NULL.
LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.
Syntax: LEFT(string,integer)
SELECT LEFT('STRING FUNCTION', 6) returns STRING
RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.
Syntax: RIGHT(string,integer)
SELECT RIGHT('STRING FUNCTION', 8) returns FUNCTION
LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)
Syntax: LTRIM(string)
SELECT LTRIM('   STRING FUNCTION') returns STRING FUNCTION
RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)
Syntax: RTRIM( string )
SELECT RTRIM('STRING FUNCTION   ') returns STRING FUNCTION
REVERSE : Returns reverse of a input string.
Syntax: REVERSE(string)
SELECT REVERSE('STRING FUNCTION') returns NOITCNUF GNIRTS
REPLICATE : Repeats a input string for a specified number of times.
Syntax: REPLICATE (string, integer)
SELECT REPLICATE(Repeat, 3) returns Repeat Repeat Repeat
SPACE : Returns a string of repeated spaces.The SPACE function is an equivalent of using REPLICATE function to repeat spaces.
Syntax: SPACE ( integer) (If integer is negative,a null string is returned.)
SELECT ('STRING') + SPACE(1) + ('FUNCTION') returns STRING FUNCTION
SUBSTRING : Returns part of a given string.
SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).
Syntax: SUBSTRING (string,startindex,length)
SELECT SUBSTRING('STRING FUNCTION', 1, 6) returns STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8) returns FUNCTION
STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.
STUFF function is useful to inserts a set of characters(string2) into a given string(string1) at a given position.
Syntax: STUFF (string1,startindex,length,string2)
SELECT STUFF('STRING FUNCTION', 1, 6, 'SQL') returns SQL FUNCTION
SELECT STUFF('SQL FUNCTION', 5, 8, 'Tutorial') returns SQL Tutorial
CHARINDEX : Returns the starting position of the specified string(string1) in a character string(string2).
Syntax: CHARINDEX (string1,string2 [,start_location ])
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 8
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 19
If string1 is not found within string2,CHARINDEX returns 0.
PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.
Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.
Syntax: PATINDEX('%pattern%',string)
SELECT PATINDEX('%SQL%','Useful SQL String Function') returns 8
SELECT PATINDEX('Useful%','Useful SQL String Function') returns 1
SELECT PATINDEX('%Function','Useful SQL String Function') returns 19
If pattern is not found within given string,PATINDEX returns 0.



Function
Parameters
Example
Description
ASCII
(character_experssion)
SELECT ASCII(‘A’)
Return 65, the ASCII code of the leftmost character ‘A’
CHAR
(integer_expression)
SELECT CHAR(65)
Return ‘A’ the character equivalent of the ASCII code value
CHARINDEX
(‘pattern’,expression)
SELECT CHARINDEX(‘E’,HELLO’)
Returns 2, the starting postion of the
DIFFERENCE
(character_expression1,
character_expression2)
SELECT DIFFRENCE(‘HELLO’,’hell’)
Return 4. The DIFFRENCE function compares two strings and evaluates the similarity between them, return a value from 0 through 4. The value 4 is the best match.
LEFT
(character_expression,
interger_expression)
SELECT LEFT(‘RICHARD’,4)
Return ‘RICH’ which is part of the character string equal in size to the integer_expression characters from the left.
LEN
(character_expression)
SELECT LEN(‘RICHARD’)
Returns ‘7, the number of characters in the character_expression.
LOWER
(character_expression)
SELECT LOWER(‘RICHARD’)
Returns ‘richard’, after converting
Character_expression to lower case.
LTRIM
(character_expression)
SELECT LTRIM(‘   RICHARD)
Return ‘RICHARD’ without leading spaces. It  removes leading blanks from the character_expression.
PATINDEX
(‘%pattern%’,expression)
SELECT PATINDEX(‘%BOX%’,’ACTION BOX’)
Return 7, the starting position of the first occurrence of the pattern in the specified expression, or zeros if the pattern is not found.
REVERSE
(character_expression)
SELECT RIGHT(RICHARD’)
Returns ‘DRAHCIR’), the reverse of the character_expression.
RIGHT
(character_expression,
integer_expression)
SELECT RIGHT(‘RICHARD’,4)
Returns ‘HARD’ , the part of the character string, extracting from the right the number of character specified in the integer_expression.
RTRIM
(character_expression)
SELECT RTRIM(‘RICHARD  ‘)
Returns ‘RICHARD’, after removing any trailing blanks from the character
Expression.
SPACE
(integer_expression,

SELECT ‘RICHARD’+ SPACE(2)+’HILL’
Returns ‘RICHARD HILL’. Two spaces are inserted between the first and second word.
STR
(float_expression,
[length,[decimal])
SELECT STR(123.45,6,2)
Returns ‘123.45’. It converts numeric data to character data where the length is the total length, incude the decimal point, the sign, the digits, and the spaces and the decimal is the number of places to the right of the decimal point.
STUFF
(character_expression1,
Start,length,
character_expression2
SELECT STUFF(‘Weather’,2,2,’i’)
Returns ‘Wither’. It deletes length character from character_expression1 from the start and then inserts char_expression2 into character_expression1 at the start position.
SUBSTRING
(expression,start,length)
SELECT SUBSTRING(‘Wether’,2,2)
Returns ‘ea’ which is part of a character string. It returns length characters from the start position of the expression.
UPPER
(character_expression)
SELECT UPPER(‘Richard’)
Retiurns ‘RICHARD’. It converts lower case character data to upper case.




No comments:

Post a Comment