Link to home
Start Free TrialLog in
Avatar of pamsauto
pamsauto

asked on

Need help getting encoding function to work

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?



Avatar of imitchie
imitchie
Flag of New Zealand image

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
Avatar of pamsauto
pamsauto

ASKER

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')

How about

select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
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.
ASKER CERTIFIED SOLUTION
Avatar of imitchie
imitchie
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent - I can sure handle it from there!