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?



pamsautoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:
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
 
imitchieCommented:
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
 
pamsautoAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
imitchieCommented:
How about

select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
0
 
pamsautoAuthor Commented:
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
 
pamsautoAuthor Commented:
Excellent - I can sure handle it from there!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.