SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Data encryption
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  01:50:13  Show Profile  Reply with Quote
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 - 06/14/2006 :  02:21:58  Show Profile  Reply with Quote
please advise
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  02:45:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use this function for very simple encryption/decryption
CREATE FUNCTION dbo.fnSimpleEncDec
(
	@StringText VARCHAR(8000),
	@PasswordCharacter CHAR(1)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	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 @ReturnText
END
To encrypt the fields, write
UPDATE MyTable
SET    first_name = dbo.fnSimpleEncDec(first_name, '?'),
       last_name = dbo.fnSimpleEncDec(last_name, '*'),
       ssn = dbo.fnSimpleEncDec(ssn, 'x')
To decrypt the fields, write
UPDATE MyTable
SET    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 using
SELECT 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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 03:41:26
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  03:21:49  Show Profile  Reply with Quote
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 w
y z a b c d e f g h i j k l m n o p q r s t u v w x
z 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


Above shown is a matrix,

Can anybody help me in creating a function where I pass field name and it has a value called Smith
Now 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 create
the function.Please help me with that
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  03:24:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Looks like a home assignment to me. For further reference, take a look at http://en.wikipedia.org/wiki/Caesar_cipher

In 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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 03:39:41
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  03:30:00  Show Profile  Reply with Quote
No..this is kind of function I need to use for scrampling data.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  03:39:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 03:41:41
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  03:45:04  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  03:50:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 QA
SELECT dbo.fnSimpleEncDec('Fzl>?Kwz?yjq|kvpq?hpmtl?xmz~k?hvkw?kzgk1', CHAR(31))
What does the function return to you?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 03:50:28
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  03:53:00  Show Profile  Reply with Quote
something like this '7ZZZZZZ'.Can u please help me with the function I have mentioned
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  03:57:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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? Write
SELECT 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 QA
SELECT 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 this
DECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BIT
SELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0

SELECT 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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 04:42:31
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/14/2006 :  04:16:40  Show Profile  Reply with Quote
"Anyone else getting the same strange result?"

Yes! The function works great with text.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  04:25:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 04:33:22
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  04:36:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 9
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 9 A
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 A B
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 B C
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 C D
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 D E
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 E F
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 F G
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 G H
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 H I
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 I J
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 J K
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 K L
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 L M
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 M N
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 N O
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 O P
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 P Q
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 Q R
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 R S
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 S T
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 T U
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 U V
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 V W
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 W X
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 X Y
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 Y Z
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 Z a
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 a b
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 b c
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 c d
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 d e
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 e f
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 f g
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 g h
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 h i
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 i j
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 j k
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 k l
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 l m
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 m n
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 n o
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 o p
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 p q
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 q r
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 r s
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 s t
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 t u
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 u v
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 v q
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 q x
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 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 z
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 z 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 z 0 0
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 0 1
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 1 2
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 2 3
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 3 4
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 4 5
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 5 6
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 6 7
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 7 8


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 04:39:16
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  05:12:55  Show Profile  Reply with Quote
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 like


DECLARE @name varchar(50)
SET @name ='smith'
DECLARE @password varchar(50)
SET @password='maskvalue'
DECLARE @Index INT
DECLARE @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 @ReturnText

This is not giving me the right answer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  05:15:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
With this,
DECLARE @InputCharacter CHAR(1), @MaskCharacter CHAR(1), @UseUpperCase BIT
SELECT @InputCharacter = 's', @MaskCharacter = 'm', @UseUpperCase = 0

SELECT 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 Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  05:25:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use this function
CREATE FUNCTION dbo.fnCaesarCipher
(
	@TextToCipher VARCHAR(8000),
	@MaskText VARCHAR(100),
	@UseUpperCase BIT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	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 @CipheredText
END


And call with
select dbo.fnCaesarCipher('HomeWorkAssignment', 'xyz', 0)


I really hope you are satisfied now when your home work assignment is done.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 05:26:19
Go to Top of Page

sqllearner
Aged Yak Warrior

639 Posts

Posted - 06/14/2006 :  05:48:07  Show Profile  Reply with Quote
Yes Sir..Thanks a lot for the help
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  06:35:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use this function to decipher the Caesar Cipher.
CREATE FUNCTION dbo.fnCaesarDecipher
(
	@TextToDecipher VARCHAR(8000),
	@MaskText VARCHAR(8000),
	@UseUpperCase BIT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	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 @DecipheredText
END
select dbo.fnCaesarDecipher('emlbunoizpqhdllbls', 'xyz', 0)


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 06:36:11
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  06:39:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 06/14/2006 :  06:41:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/14/2006 06:55:22
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000