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)

Create your own key in Registry using Microsoft.Win32.RegistryKey in c#.Net

//Check for key exists in Registry before creating New key and if not exists then create new key

if (((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))) == null)

{

Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.CreateSubKey("MyChandruKey123");

key.SetValue("MyConnStrChandru123","");

}

//Get the Key value based on key name

string conns = ((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))).GetValue("MyConnStrChandru123").ToString();

Encrypt and Decrypt the string using RSACryptoServiceProvider in c#.Net within Single Line of Code

//Call the functions

string EncryptedString= GetEncryptedText("Chandru");

string DecryptedString = GetDecryptedText(EncryptedString);

//Create an object for RSACryptoServiceProvider

RSACryptoServiceProvider RSAEncrypt = new RSACryptoServiceProvider();

//Encrypt the String Provided

public string GetEncryptedText(string PlainStringToEncrypt)

{

return Encoding.Default.GetString(RSAEncrypt.Encrypt(Encoding.Default.GetBytes(PlainStringToEncrypt), false));

}

//Decrypt The String Provided

public string GetDecryptedText(string EncryptedStringToDecrypt)

{

return Encoding.Default.GetString(RSAEncrypt.Decrypt(Encoding.Default.GetBytes(EncryptedStringToDecrypt), false));

}

Access Table withought linking Servers

Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',

'SELECT * FROM pubs.dbo.authors') AS A Where A.MaintenanceDate <> MaintenanceDate

Select A.* From OPENROWSET('MSDASQL',

'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass',

'SELECT * FROM database.dbo.table') as A

Right Outer Join database.dbo.table As B on A.PlantID = B.PlantID

AND A.RuleType = B.RuleType AND A.RulePrefix = B.RulePrefix and

A.RuleSuffix = B.RuleSuffix

Where A.MaintenanceDate <> B.MaintenanceDate




SELECT A.*
FROM database.dbo.table AS B
LEFT JOIN OPENROWSET('MSDASQL', 'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass'
,'SELECT * FROM database.dbo.table') AS A
ON B.PlantID = A.PlantID AND B.RuleType = A.RuleType AND B.RulePrefix = A.RulePrefix
AND B.RuleSuffix = A.RuleSuffix AND B.MaintenanceDate <> A.MaintenanceDate