Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 01:50:13
|
I want to change the first_name and last_name field values to some other values so that some other persons looking at will have a different first and last name.The same with the SSN number and another thing is I need to know the reverse logic to get back these values if needed.This is just for security reasons.Please advise. |
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 02:21:58
|
please advise |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 02:45:09
|
Use this function for very simple encryption/decryptionCREATE FUNCTION dbo.fnSimpleEncDec( @StringText VARCHAR(8000), @PasswordCharacter CHAR(1))RETURNS VARCHAR(8000)ASBEGIN DECLARE @Index SMALLINT, @ReturnText VARCHAR(8000) SELECT @Index = DATALENGTH(@StringText), @ReturnText = '' WHILE @Index > 0 SELECT @ReturnText = CHAR(ASCII(SUBSTRING(@StringText, @Index, 1)) ^ ASCII(@PasswordCharacter)) + @ReturnText, @Index = @Index - 1 RETURN @ReturnTextEND To encrypt the fields, writeUPDATE MyTableSET first_name = dbo.fnSimpleEncDec(first_name, '?'), last_name = dbo.fnSimpleEncDec(last_name, '*'), ssn = dbo.fnSimpleEncDec(ssn, 'x') To decrypt the fields, writeUPDATE MyTableSET first_name = dbo.fnSimpleEncDec(first_name, '?'), last_name = dbo.fnSimpleEncDec(last_name, '*'), ssn = dbo.fnSimpleEncDec(ssn, 'x') You can use whatever character you like as password. There is one caveat or drawback! You can't use a password character that normally exist in the field, such as 'e'. If you do, SQL server truncates the string at the position where field contains same character as password character used.For SSN you can use whatever character not used for SSN, such as '*', '?' or 'x'. For names, I would prefer usingSELECT dbo.fnSimpleEncDec(first_name, CHAR(31)) because then I will XOR 5 bits out of 8 for every character in the text to be encrypted or decrypted! And the text still looks somewhat normal.Another exiting password character is CHAR(255)! That makes the field text go "inverse".Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 03:21:49
|
a b c d e f g h i j k l m n o p q r s t u v w x y zb c d e f g h i j k l m n o p q r s t u v w x y z ac d e f g h i j k l m n o p q r s t u v w x y z a bd e f g h i j k l m n o p q r s t u v w x y z a b ce f g h i j k l m n o p q r s t u v w x y z a b c df g h i j k l m n o p q r s t u v w x y z a b c d eg h i j k l m n o p q r s t u v w x y z a b c d e fh i j k l m n o p q r s t u v w x y z a b c d e f gi j k l m n o p q r s t u v w x y z a b c d e f g hj k l m n o p q r s t u v w x y z a b c d e f g h ik l m n o p q r s t u v w x y z a b c d e f g h i jl m n o p q r s t u v w x y z a b c d e f g h i j km n o p q r s t u v w x y z a b c d e f g h i j k ln o p q r s t u v w x y z a b c d e f g h i j k l mo p q r s t u v w x y z a b c d e f g h i j k l m np q r s t u v w x y z a b c d e f g h i j k l m n oq r s t u v w x y z a b c d e f g h i j k l m n o pr s t u v w x y z a b c d e f g h i j k l m n o p qs t u v w x y z a b c d e f g h i j k l m n o p q rt u v w x y z a b c d e f g h i j k l m n o p q r su v w x y z a b c d e f g h i j k l m n o p q r s tv w x y z a b c d e f g h i j k l m n o p q r s t uw x y z a b c d e f g h i j k l m n o p q r s t u vx y z a b c d e f g h i j k l m n o p q r s t u v wy z a b c d e f g h i j k l m n o p q r s t u v w xz a b c d e f g h i j k l m n o p q r s t u v w x yAbove shown is a matrix,Can anybody help me in creating a function where I pass field name and it has a value called SmithNow I have code say 'masktext'Now the function should compare 'S' and 'M' and take the join which is 'E' and now take the next one'M' and 'A' which is 'M'Like that it should take the join and create a word so that Thats my encrypted value for Smith.Iam finding difficult to createthe function.Please help me with that |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 03:24:13
|
Looks like a home assignment to me. For further reference, take a look at http://en.wikipedia.org/wiki/Caesar_cipherIn your example above, how do you encrypt numbers and spaces such as Street addresses?110 North Street or names that looks like "Mathers Junior" or "Astor III"Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 03:30:00
|
No..this is kind of function I need to use for scrampling data. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 03:39:23
|
quote: Originally posted by sqllearner No..this is kind of function I need to use for scrampling data.
And my function I posted earlier does not work for you?Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 03:45:04
|
The one posted earlier is excellent but for my requirement it should be replaced with text values (when I use the function iam getting ascii values)and the above shown is a proposed method as well. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 03:50:00
|
quote: Originally posted by sqllearner The one posted earlier is excellent but for my requirement it should be replaced with text values (when I use the function iam getting ascii values)and the above shown is a proposed method as well.
Strange. My function above returns text VARCHAR(8000), not numeric values.Try writing in QASELECT dbo.fnSimpleEncDec('Fzl>?Kwz?yjq|kvpq?hpmtl?xmz~k?hvkw?kzgk1', CHAR(31)) What does the function return to you?Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 03:53:00
|
something like this '7ZZZZZZ'.Can u please help me with the function I have mentioned |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 03:57:53
|
quote: Originally posted by sqllearner something like this '7ZZZZZZ'.Can u please help me with the function I have mentioned
That result should not be possible, unless you are using UNICODE.What happens when you try to decode the text? WriteSELECT dbo.fnSimpleEncDec('7ZZZZZZ', *the password character you used before to encrypt*) Anyone else getting the same strange result? Please post your result here of the query.Write following query in QASELECT dbo.fnSimpleEncDec(CONVERT(VARCHAR(8000), 'Fzl>?Kwz?yjq|kvpq?hpmtl?xmz~k?hvkw?kzgk1'), CONVERT(VARCHAR, CHAR(31))) At the page for which I sent you a link, there are examples for writing code for Caesar Cipher.Otherwise use algorithm like thisDECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BITSELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0SELECT CHAR((ASCII(LOWER(@InputCharacter)) + ASCII(LOWER(@MaskCharacter)) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END) You are aware that the function you need is irreversible? Text like "North Street" will never look the same again efter first encrypting and then decrypting? "North Street" will look like "north street" or (with same change to function) "NORTH STREET".Peter LarssonHelsingborg, Sweden |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-14 : 04:16:40
|
"Anyone else getting the same strange result?"Yes! The function works great with text. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 04:25:04
|
quote: Originally posted by Kristen "Anyone else getting the same strange result?"Yes! The function works great with text.
Thanks Kristen. I knew I could count on you Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 04:36:01
|
This is the substitution schema used, just for allowing A-Z, a-z, space and 0-9. A 63 by 63 matrix. Allowing substition of complete ASCII set, is a 256 by 256 matrix that is 16 times larger than this one.A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 1 2 3 4 5 6 7 8 9B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 AC D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A BD E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B CE F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C DF G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D EG H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E FH I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F GI J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G HJ K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H IK L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I JL M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J KM N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K LN O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L MO P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M NP Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N OQ R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O PR S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P QS T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q RT U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R SU V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S TV W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T UW X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U VX Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V WY Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W XZ a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Ya b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Zb c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z ac d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a bd e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b ce f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c df g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d eg h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e fh i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f gi j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g hj k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h ik l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i jl m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j km n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k ln o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l mo p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m np q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n oq r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o pr s t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p qs t u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q rt u v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r su v q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s tv q x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t uq x y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u vx y z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v qy z 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q xz 0 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 01 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 02 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 13 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 24 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 35 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 46 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 57 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 68 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 79 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v q x y z 0 0 1 2 3 4 5 6 7 8 Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 05:12:55
|
How can I create a function out of the matrix given above which compares the field value like name 'smith' to one mask password value like 'maskvalue.So the comparison would be 's' to 'm' and 'm' to 'a' and 'i' to 's'...likewise.Please advise a function something likeDECLARE @name varchar(50)SET @name ='smith'DECLARE @password varchar(50)SET @password='maskvalue'DECLARE @Index INTDECLARE @ReturnText varchar(1000) SELECT @Index = 1, @ReturnText = ''PRINT @index WHILE @Index <= DATALENGTH(@name) SELECT @ReturnText = CASE WHEN SUBSTRING(@name, @Index, 1)IN ('a') Then 'm' WHEN SUBSTRING(@name, @Index, 1)IN ('b') Then 'n' WHEN SUBSTRING(@name, @Index, 1)IN ('c') Then 'o' WHEN SUBSTRING(@name, @Index, 1)IN ('d') Then 'p' WHEN SUBSTRING(@name, @Index, 1)IN ('e') Then 'q' WHEN SUBSTRING(@name, @Index, 1)IN ('f') Then 'r' WHEN SUBSTRING(@name, @Index, 1)IN ('g') Then 's' WHEN SUBSTRING(@name, @Index, 1)IN ('h') Then 't' WHEN SUBSTRING(@name, @Index, 1)IN ('i') Then 'u' WHEN SUBSTRING(@name, @Index, 1)IN ('j') Then 'v' WHEN SUBSTRING(@name, @Index, 1)IN ('k') Then 'w' WHEN SUBSTRING(@name, @Index, 1)IN ('l') Then 'x' WHEN SUBSTRING(@name, @Index, 1)IN ('m') Then 'y' WHEN SUBSTRING(@name, @Index, 1)IN ('n') Then 'z' WHEN SUBSTRING(@name, @Index, 1)IN ('o') Then 'a' WHEN SUBSTRING(@name, @Index, 1)IN ('p') Then 'b' WHEN SUBSTRING(@name, @Index, 1)IN ('q') Then 'c' WHEN SUBSTRING(@name, @Index, 1)IN ('r') Then 'd' WHEN SUBSTRING(@name, @Index, 1)IN ('s') Then 'e' WHEN SUBSTRING(@name, @Index, 1)IN ('t') Then 'f' WHEN SUBSTRING(@name, @Index, 1)IN ('u') Then 'g' WHEN SUBSTRING(@name, @Index, 1)IN ('v') Then 'h' WHEN SUBSTRING(@name, @Index, 1)IN ('w') Then 'i' WHEN SUBSTRING(@name, @Index, 1)IN ('x') Then 'j' WHEN SUBSTRING(@name, @Index, 1)IN ('y') Then 'k' WHEN SUBSTRING(@name, @Index, 1)IN ('z') Then 'l' ELSE 's' END + @ReturnText,@Index = @Index + 1 PRINT @ReturnTextThis is not giving me the right answer |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 05:15:36
|
With this,DECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BITSELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0SELECT CHAR((ASCII(LOWER(@InputCharacter)) + ASCII(LOWER(@MaskCharacter)) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END) I am pointing you out in the right direction.Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 05:25:45
|
Use this functionCREATE FUNCTION dbo.fnCaesarCipher( @TextToCipher VARCHAR(8000), @MaskText VARCHAR(100), @UseUpperCase BIT)RETURNS VARCHAR(8000)ASBEGIN DECLARE @Index SMALLINT, @CipheredText VARCHAR(8000) SELECT @MaskText = REPLICATE(@MaskText, 1 + LEN(@TextToCipher) / LEN(@MaskText)), @Index = DATALENGTH(@TextToCipher), @CipheredText = '' WHILE @Index > 0 SELECT @CipheredText = CHAR((ASCII(LOWER(SUBSTRING(@TextToCipher, @Index, 1))) + ASCII(LOWER(SUBSTRING(@MaskText, @Index, 1))) - 194) % 26 + CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END) + @CipheredText, @Index = @Index - 1 RETURN @CipheredTextEND And call withselect dbo.fnCaesarCipher('HomeWorkAssignment', 'xyz', 0) I really hope you are satisfied now when your home work assignment is done.Peter LarssonHelsingborg, Sweden |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-06-14 : 05:48:07
|
Yes Sir..Thanks a lot for the help |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 06:35:29
|
Use this function to decipher the Caesar Cipher.CREATE FUNCTION dbo.fnCaesarDecipher( @TextToDecipher VARCHAR(8000), @MaskText VARCHAR(8000), @UseUpperCase BIT)RETURNS VARCHAR(8000)ASBEGIN DECLARE @Index SMALLINT, @DecipheredText VARCHAR(8000) SELECT @MaskText = REPLICATE(@MaskText, 1 + LEN(@TextToDecipher) / LEN(@MaskText)), @Index = DATALENGTH(@TextToDecipher), @DecipheredText = '' WHILE @Index > 0 SELECT @DecipheredText = CHAR(CASE WHEN @UseUpperCase = 1 THEN 65 ELSE 97 END + ((26 + ASCII(LOWER(SUBSTRING(@TextToDecipher, @Index, 1))) - ASCII(LOWER(SUBSTRING(@MaskText, @Index, 1)))) % 26)) + @DecipheredText, @Index = @Index - 1 RETURN @DecipheredTextEND select dbo.fnCaesarDecipher('emlbunoizpqhdllbls', 'xyz', 0)Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 06:39:04
|
You will have to write your logic yourself to implement some security features, such as stripping off all other characters than a-z in the function.Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-14 : 06:41:55
|
quote: Originally posted by sqllearner No..this is kind of function I need to use for scrampling data.
It's better if you are true to us who helps you. A clever person would not use an irreversible function to scramble data "for security reasons" when also asking for "reverse logic".However, it was fun to write the functions. Hopefully someone else might also benifit from them.Peter LarssonHelsingborg, Sweden |
|
|
Next Page
|
|
|
|
|