Prasad's Blog

How to Encrypt and Decrypt in SQL Server with Certificate: While using the sensitive data in applications, we need to encrypt it whenever we are storing in the database tables. For this purpose, we usually use the asymmetric keys with certificate. The process is descriped as below... First, we need to create as master key with a password. Later, we need to create a certificate with Subject Name and Expiry Date. Later, we need to create a symmetric key with a algorithm by making use of the certificate. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd' CREATE CERTIFICATE MyCertCert WITH SUBJECT = 'MyTestCert', EXPIRY_DATE = '10/31/2018' CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCertCert Once the key is created with the certificate, you can see your certificate by making use of the below query. SELECT * FROM sys.certificates Later you can use the below SQL Statements to encrypt or decrypt the cont...

SQL Server String Manipulations: We can use simple regular expressions for "LIKE" to filter as wild card characters.We can use [0-9] for digits and [a-z] for characters. Eg: to filter a 4 digit ZIP code we can use " where ZIP like '[0-9][0-9][0-9][0-9]'"       to filter the string that starts with digit we can use " where name like '[0-9]%'" Like same, we can use ^ for the not match condition and _ for matching one character condition. Eg: to filter names that don't start with p then we can use " where name like '[^p]%'"       to filter names that end with ir then we can use " where name like '_ir'" We can find the index of any character in a string expression with CHARINDEX function. To find the last index of any character, we can use the below... datalength(filename)-charindex('.',reverse(filename),1)