If at first you don't succeed, call it version 1.0

Contact Me

Sarvesh Kushwaha
Email : sarveshkushwaha@outlook.com

Total Pageviews

Powered by Blogger.

Friday 5 October 2012

SQL string Functions Tutorial (SQL Server 2005 -2012)


Below are all SQL string functions and they are very commonly used according to requirements. i tried to describe them in an easy manner and puts my best efforts to make them understandable .


1.ASCII function returns the ASCII code value from the leftmost character
 specified character expression. It takes 1 argument as string/character expression.


Example:select ASCII('A') or
select ASCII('AD')    
//giving ASCII value of leftmost character i.e 'A'
Result: 65 

2. CHAR function is just opposite of ASCII , it takes 1 Integer argument and as a result gives ASCII value.

Example : Select CHAR(68)
Result :  68

3. CHARINDEX string function takes 3 arguments. 1st argument specifies the character whose index is to be retrieved  and 2nd argument takes as a string from which character index is to find out and last argument is optional i.e 3rd argument is from which index to start searching.



Syntax: CHARINDEX ( expressionToFind ,expressionToSearch , start_location ) 
Example: Select CHARINDEX ('S','MICROSOFT SQL SERVER 2012') or
Select CHARINDEX ('S','MICROSOFT SQL SERVER 2012',4)
Result: 6

4.CONCAT function is to concatenate two or more strings.

//Added in SQL Server 2012
Syntax:CONCAT ( string_value1, string_value2 [, string_valueN ] )
Example :select CONCAT('I','LOVE','YOU')
Result : I LOVE YOU

5. DIFFERENCE  function shows the difference between the SOUNDEX values of two character expressions. takes two arguments both are chracters and reults the differnce in integer.Its very helpfull when we have to provide SOUNDEX search in any project or application.


Syntax : DIFFERENCE ( character_expression , character_expression )
Example : SELECT SOUNDEX('Sarvesh'), SOUNDEX('Vindesh'), DIFFERENCE('Sarvesh', 'Vindesh')
Result : 1
6. FORMAT function returns the value with specified format and cultures availble in SQL Server 2012.
Best use of format function is show the date/time country wise and number values as string .For general data type conversions, use CAST or CONVERT.format function takes 3 argument:
1st for supported data types in sql server 2012 ,
2nd for nvarchar supported format pattern.
3rd for Culture .


Syntax : FORMAT ( value, format , culture  )
Example :
DECLARE @d DATETIME = '01/01/2012';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
Result : 1/1/2012 

Example 2: SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
Result : Saturday, January 01, 2012

7. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter.
It returns first characters of specified length starting from the left side of the string entered as 1st argument.


Syntax : LEFT ( character_expression , integer_expression )
Example: Select LEFT ('MICROSOFT SQL SERVER 2012',4)
Result : MICR
8. LEN string function takes 1 argument as string value and returns the length of entered string.


Example : Select LEN ('MICROSOFT SQL SERVER 2012')
Result : 25

9. LOWER string function returns the lower case string of provided upper string or any string.
  It takes 1 argument as string value.


Syntax:  LOWER ( character_expression )
Example : select LOWER('HELP EVERYONE')
Result : help everyone
10.UPPER string function returns the upper case string of provided lower string or any string.
It takes 1 argument as string value.


Syntax: UPPER ( character_expression )
Example: select UPPER(' learning is endless process')
Result: LEARNING IS ENDLESS PROCESS

11. LTRIM function removes all the blank spaces at left side of given string. It also takes 1 argument as string value.


Syntax : LTRIM ( character_expression )
Example : select LTRIM (' Girlfriend ')
Result : Girlfriend_
blanks at the right side did'nt get removed. and you can't trim your GF from anyside :P :P :D.
12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.


Syntax: RTRIM ( character_expression )
Example: select RTRIM (' Girlfriend  ')
Result : _Girlfriend
blanks at the left did'nt get removed.


13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.


Syntax: PATINDEX ( '%pattern%' , expression )
Example: select PATINDEX('%SER%','SQL SERVER')
Results: 5   // it includes space as well ;) .



14. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string in 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.


Syntax: REPLACE ( string_expression , string_pattern , string_replacement )
Example: Select REPLACE ('Dont Respect Girls','Dont','Always')
Result: Always Respect Girls


15.REPLICATE string function Repeats a string value a specified number of times.
Syntax: REPLICATE ( string_expression ,integer_expression ) 
Example: select REPLICATE('GF',4)
Result: GFGFGFGF //now you have Four GirlFriends :P :P.

16. REVERSE string function returns the Provided string in reverse order. It takes 1 argument as string value.


Synatx: REVERSE ( string_expression )
Example: select REVERSE('Kanjar')
Result: rajnaK   //Uppercase and loweraces remains as it was .


17.RIGHT string function takes 2 arguments. 1st argument takes as a string value
and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.


Syntax: RIGHT ( character_expression , integer_expression )
Example: Select RIGHT ('MICROSOFT SQL SERVER 2012',4)
Result: 2012

18.SOUNDEX
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
Syntax: SOUNDEX ( character_expression )
Example: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');
Result:   S530     S530  //sound similarity

19.SPACE function Returns a string of repeated spaces.SPACE function can be used to povide
spacing in b/w words.SPACE takes one argument .
Syntax: SPACE ( integer_expression )
Exmaple: select 'YOU' + SPACE(10) + 'YOUR LOVE'
Result: YOU          YOUR LOVE    // in real reduce this space ;) 



20. STR function returns character data converted from numeric data. It takes 3 arguments.
1st argument as float data
2nd argument as integer value specifying the length of the string including decimal that is to be retrieved.
3rd argument as integer specifying the number of places to the right of the decimal point.


Syntax: STR ( float_expression  , length  , decimal )
Example: select STR(140.15, 6, 1)
Result : 140.2   // if your  2nd arugment length exceed from 1st then it returns **


21.STUFF string function takes 4 arguments. It is used to replace specified length of characters with provided pattern.
1st argument as string value.
2nd argument as integer value specifying the starting point of characters to be replaced.
3rd arguments as integer value specifying the length of characters.
4th argument as string value specifying the new pattern of characters.


Syntax: STUFF ( character_expression , start , length , replaceWith_expression )
Example: Select STUFF ('MICROSOFT SQL SERVER 2012', 11, 3,'S.Q.L.')
Result: MICROSFT S.Q.L. SERVER 2012

22.SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length


Syntax: SUBSTRING ( expression ,start , length )
Example: Select SUBSTRING ('MICROSOFT SQL SERVER 2012', 11, 3)
Result: SQL

23.QUOTENAME function Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )

24.NCHAR Function Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
Syntax: NCHAR ( integer_expression )

25.UNICODE Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
Syntax: UNICODE ( 'ncharacter_expression' )

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete