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(@InputStri ng, @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)+'-'+RTR IM(SUBSTRI NG(@Result ,4,7))+SUB STRING(@Re sult,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(@InputStri ng, @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('1160210 5R')
it should return 0x525A425D0208010219157C
but it returns 0x525A425D020801026B150E
What am I missing here?
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(@InputStri
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,
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(@InputStri
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('1160210
it should return 0x525A425D0208010219157C
but it returns 0x525A425D020801026B150E
What am I missing here?
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.encryptf ullhn('116 02105R')
select * from IndexList where Encryptedcode=dbo.encryptf
How about
select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent - I can sure handle it from there!
Try
select dbo.decrypthn( 0x525A425D020801026B150E )
select dbo.decrypthn( dbo.encryptfullhn( '11602105R ' ) )
you will see that the result is correct