MSSQL Hash Algorithms

MSSQL Hash Algorithms:

There are two forms of encryption algorithms: one-way and two-way. Two-way algorithms allow you to encrypt and decrypt data. One-way algorithms will only encrypt data without any ability to decrypt.

A hash algorithm is the simplest way to encrypt data, however, once encrypted, you can’t decrypt the data. Your data remains secure, ensuring that in order to “decrypt” the contents of a column you would first have to know the original data value.

--Different hash algorithms produce different hash values
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('MD5', @TEST_VAL)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

--Hash values are case sensitive
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'sql'
SELECT HashBytes('SHA1', @TEST_VAL)
SELECT @TEST_VAL = 'SQL'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

Advertisements

SUBSTRING Function

See the Uses of SUBSTRING, CHARINDEX, REPLACE Functions in MSSQL.


SELECT
'3/6/2010' as OLD_FORMAT,
charindex('/','3/6/2010',1) [FIRST/POSITION],
charindex('/','3/6/2010',3) [2nd/POSITION],
replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','') as MM,
replace(left('3/6/2010',2),'/','') as DD,
right('3/6/2010',4) as YYYY,

replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','')+'/'+
replace(left('3/6/2010',2),'/','')+'/'+
right('3/6/2010',4) as NEW_FORMAT

Example to convert date  dd/mm/yyyy to mm/dd/yyyy