August 28, 2008 at 1:49 pm
In a table if there is a column with last name Example joe :it should be replaced as
A-to be replaced by B
B-->c
C-->d
D-->
basically leter should be incremented by one
so A-->B
B-->c
B-->C
A-->B
R-->S
a-->b
j-->k
u-->v
So with this joe will be or should be replaced by -->kpf
i could do it for one character but unable to do it as a whole word..can someone help me
DECLARE @chOneLetter char(1)
Set @chOneLetter = 'F'
SELECT @chOneLetter = CHAR(ASCII(@chOneLetter) + 1)
print @chOneLetter
August 28, 2008 at 2:37 pm
What did you need to do with z and Z ?
August 28, 2008 at 3:02 pm
There are other ways to obfuscate the data.
N 56°04'39.16"
E 12°55'05.25"
August 28, 2008 at 3:33 pm
What the other poster said about obfuscating -- but here you go:
CREATE FUNCTION [dbo].[fnObfuscateString]
(
@strInput VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @chOneLetter CHAR(1);
DECLARE @strOutput VARCHAR(255);
DECLARE @index INT;
-- Initialize
SELECT @index = 1, @strOutput = '';
-- Do Until Done With String
WHILE (@index <= LEN(@strInput))
BEGIN
-- Get This Byte
SELECT @chOneLetter = SUBSTRING(@strInput, @index, 1);
-- Obfuscate, and Append to the Output String
SELECT @strOutput = @strOutput + CHAR(ASCII(@chOneLetter) + 1);
-- Increment Index
END
-- Return the Output String
RETURN @strOutput;
END
GO
-- Test
SELECT [dbo].fnObfuscateString('joe')
-- Output:
kpf
August 28, 2008 at 3:41 pm
Hey guys thanks a lot for all your help.......i appreciate the same...
i found another way of doing it .
here is the code.
DECLARE @LEN INT,@chOneLetter char(10),@string varchar(50),@initial int,@counter int,@Finalans varchar(50)
Set @string ='apparao'
SELECT
@LEN=LEN(@string)
SELECT @chOneLetter = CHAR(ASCII(@chOneLetter)+1)
SET @initial=1
set @finalans=''
while (@initial<=@LEN)
Begin
--Set @ChOneletter=substring(@string,@i,1)
SELECT
@chOneLetter= SUBSTRING(@string,@initial,1)
select @chOneLetter =case
when @chOneLetter ='h' then 'i'
when @chOneLetter ='i' then 'j'
when @chOneLetter ='j' then 'k'
when @chOneLetter ='k' then 'l'
when @chOneLetter ='l' then 'm'
when @chOneLetter ='m' then 'n'
when @chOneLetter ='n' then 'o'
when @chOneLetter ='o' then 'p'
when @chOneLetter ='p' then 'q'
when @chOneLetter ='q' then 'r'
when @chOneLetter ='r' then 's'
when @chOneLetter ='s' then 't'
when @chOneLetter ='t' then 'u'
when @chOneLetter ='u' then 'v'
when @chOneLetter ='v' then 'w'
when @chOneLetter ='w' then 'x'
when @chOneLetter ='x' then 'y'
when @chOneLetter ='y' then 'z'
when @chOneLetter ='z' then 'a'
when @chOneLetter ='a' then 'b'
when @chOneLetter ='b' then 'c'
when @chOneLetter ='c' then 'd'
when @chOneLetter ='d' then 'e'
when @chOneLetter ='e' then 'f'
when @chOneLetter ='f' then 'g'
when @chOneLetter ='g' then 'h'
--else ''
end
select @finalans=@finalans+@chOneLetter
SET
@initial=@initial+1
END
SELECT
@finalans
August 28, 2008 at 3:51 pm
DECLARE@Sample TABLE (c CHAR(1))
INSERT@Sample
(
c
)
SELECTCHAR(Number)
FROMmaster..spt_values
WHEREType = 'P'
AND Number BETWEEN 65 AND 90
SELECTc,
CHAR(65 + (ASCII(c) - 64) % 26)
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
August 28, 2008 at 4:49 pm
thats another cool way by sscommitted....thank you....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply