Solved

Need help getting encoding function to work

Posted on 2007-12-04
6
300 Views
Last Modified: 2012-05-05
OK, I have two functions in SQL.   Once function encodes text into a binary data type and the other decodes the data.   This all seems to work great except for when the text has an "R" on the end.   Here is the code for both functions.

CREATE FUNCTION [dbo].[DecryptHN] (@InputString  varchar(11))  
RETURNS varchar (12)
 AS  
BEGIN

declare @ctr tinyint
declare @master varchar(32)
declare @TempKey varchar(255)
declare @Result varchar(32)
declare @FinalResult varchar(12)
declare @Tchar int
declare @Mchar int
declare @strlen int


select @strlen =  LEN(RTRIM(@InputString))

set @TempKey = 'cktm091795.'

set @ctr = 1
set @Result = ''

WHILE @ctr <= @strlen
   BEGIN
   set @Mchar = ASCII(SUBSTRING(@InputString, @ctr, 1))
   set @Tchar = ASCII(SUBSTRING(@TempKey, @ctr, 1))
   set @Result = @Result + cast(char(@Mchar ^ @Tchar)as varchar(32))
   SELECT @ctr = @ctr + 1
   if @ctr > @strlen
     BREAK
 
   END
   Set @FinalResult=LEFT(@Result,3)+'-'+RTRIM(SUBSTRING(@Result,4,7))+SUBSTRING(@Result,11,1)
Return (@FinalResult)

END

and the other function.


CREATE FUNCTION [dbo].[EncryptFullHN] (@InputString  char(11))  
RETURNS binary (11)
 
BEGIN

declare @ctr tinyint
declare @master varchar(32)
declare @TempKey varchar(255)
declare @Result varchar(32)
declare @Tchar int
declare @Mchar int
declare @BinResult binary(11)

set @TempKey = 'cktm091795.'

set @ctr = 1
set @Result = ''
WHILE @ctr <= datalength(@InputString)
   BEGIN
     set @Mchar = ASCII(SUBSTRING(@InputString, @ctr, 1))
     set @Tchar = ASCII(SUBSTRING(@TempKey, @ctr, 1))
     set @Result = @Result + cast(char(@Mchar ^ @Tchar)as varchar(32))
     SET @ctr = @ctr + 1
END

set @BinResult = CAST (@Result AS binary(11) )

Return (@BinResult)


END


To test this you can run this SQL.

select dbo.encryptfullhn('11602105R')

it should return 0x525A425D0208010219157C
but it returns    0x525A425D020801026B150E

What am I missing here?



0
Comment
Question by:pamsauto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20405714
There is no difference, because your input string is not 11 chars, the end becomes undefined.
Try

select dbo.decrypthn( 0x525A425D020801026B150E )
select dbo.decrypthn( dbo.encryptfullhn( '11602105R  ' ) )

you will see that the result is correct
0
 

Author Comment

by:pamsauto
ID: 20405749
It does give results in that test that would seem to be ok,but the issue is I have a table of encrypted codes, and I encrypt the text and then use that in a select statement, so I need the results to be exact of course.   The function is used like this.

select * from IndexList where Encryptedcode=dbo.encryptfullhn('11602105R')

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20405854
How about

select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pamsauto
ID: 20405927
Yeah, except that the table has 8 million rows in it and it would take quite a while to run that query.........   What do you mean the end becomes undefined in your first answer?   I have to be honest, I don't undersatnd how the encoding works, and if I did I could probably figure this out.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20406238
Okay, the problem is that there could be MORE than one EncryptedCode that will decrypt to a particular string, i.e.

select dbo.decrypthn( 0x525A425D0208010219157C )
select dbo.decrypthn( 0x525A425D020801026B150E )

both return '116-02105R'
due to this line in decrypt:
   Set @FinalResult=LEFT(@Result,3)+'-'+RTRIM(SUBSTRING(@Result,4,7))+SUBSTRING(@Result,11,1)

If the string contains SPACES in the raw decrypted result between positions 5-10, those spaces disappear.  To get 0x525A425D0208010219157C, use

select dbo.encryptfullhn('11602105  R')

So in fact,

select * from IndexList where Encryptedcode=dbo.encryptfullhn('11602105R')

CORRECTLY returns no results, because 0x525A425D0208010219157C is not encrypted from that string
0
 

Author Comment

by:pamsauto
ID: 20406335
Excellent - I can sure handle it from there!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question