Tuesday, June 30, 2009

Different String Function in SQL Server 2005

ASCII function in SQLServer

Returns the ASCII code value of Leftmost charector in a string
Syntax : ASCII ('char Expression')

NCHAR
function in SQLServer
Returns the Unicode Charector with specified Integer code, as defined be Unicode standard
Syntax :NCHAR(Integer Expression)

SOUNDEX
function in SQLServer
Returns a four-charector code to evalute the similarity of two strings
Syntax : SOUNDEX('char Expression')
Ex:
Select SOUNDEX('smith'),SOUNDEX('smythe')
Result:
S530 S530

CHAR
function in SQLServer
Convert an Int ASCII code to a Charector
Syntax : CHAR( Integer Expression )
Note : Is an integer from 0 through 255, NULL is return if the integer expression not in the Range

PATINDEX
function in SQLServer
Returns the starting position of the first occurance of a pattren in the specied expression, or Zero if the pattren is nor found the specified expression, on all valid text and charector data types.
Syntax : PATINDEX('%pattren%',expression)

SPACE
function in SQLServer
Returns a string of Repeated Spaces
Syntax : SPACE (Integer Expression)
Ex:
Select 'Myname' + Space(2) + 'Lastname'
Result:
Myname Lastname

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

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

' quote_character '

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

Ex:
Select QUOTENAME('abc[]def')
Result :
[abc[]]def

STR
function in SQLServer
Returns characters data converted from numaric data
Syntax : STR (float Expression [,length,[[decima]])
Ex;
Select STR(123.45,6,1)
Result
123.4

DIFFERENCE
function in SQLServer
Retruns interger value that indicates the difference between SOUNDEX value of two character expressions
Syntax : DIFFERENCE (firat expression, second Expression)

REPLACE function in SQLServer
Replaces all occurences of Specified string value with another string value
Syntax : Replace (car expression,old string,new string)
EX:
Print REPLACE('abcdefghcd','c','x')
Result:
abxdefghxd

STUFF function in SQLServer
Deletes a specied length of characters and insert another set of characters at specified starting point
Syntax : STUFF(char expression,start,length,char expression)
Ex :
select STUFF('abcdef',2,3,'ijklmn')
Result:
aijklmnef


LEFT function in sqlserver
Return the left part of the char expression with specied number of characters
Syntax : LEFT(char expression,length)
Ex:
print LEFT('abcdef',2)
Result:
ab

REPLICATE function in SQLServer
Replicate a string value in specified numver of times
Syntax : REPLICATE(char expression, interger expression)
Ex:
Print REPLICATE('0',4) + 'S'
Result:
0000S

SUBSTRING function in SQL Server
Returns a part of charecter, byte, text or Image expression
Syntax : SUBSTRING(char expression,start,length)
Ex:
select SUBSTRING('myname',1,2)
Result:
my

LEN function in SQL Server
Returns length of specified char expression, excluding trailing blank spaces
Syntax : LEN(char expression)
Ex :
print LEN(' my Name')
Result :
7


REVERSE function in SQL Server
Returns reverse of char expression
Syntax : REVERSE(char expression)

No comments: