July 24, 2012 at 2:41 pm
Hello all, I am a new SQL programmer and a pretty new forum member. I apologize if this question is somewhat basic for you veterans out there, but I am having some trouble with my update statement updating a certain column, known as SSN. Currently I am updating the column with an update statement that looks like this:
UPDATE Dev.dbo.Contact SET SSN = DBA.dbo.character_scramble(SSN)
You will notice that I am updating the column using a function, character_scramble, where I am passing in the SSN column and scrambling it. The code for character_scramble is posted below. The problem that I am having is ... when I run the update statement it is returning an error from the column SSN, the error reads, "String or binary data would be truncated". I know that this is usually caused when a value is too large for it's assigned column, but how could that be? In theory the character_scramble function should take only the characters from that column and scramble them, correct? If this is the case, then why would it be returning the error "String or binary data would be truncated"? The SSN column is a column in the Contact table and is a nvarchar of length (10).
The primary purpose of this scramble is for data obfuscation purposes, but that is of relative importance. Please comment and let me know if you have any ideas or solutions to my problem. I am kind of beating my head against a wall here because it works for every other column in the database just fine, naturally the one that I want it to run on, it doesn't work. Thanks in advance for the help!
--drop function dbo.character_scramble
create function [dbo].[character_scramble]
(
@originalVal varchar(max)
)
returns varchar(max)
as
begin
declare @newVal varchar(max);
declare @origLen int;
declare @currLen int;
declare @loopCt int;
declare @random int;
set @newVal = '';
set @origLen = datalength(@originalVal);
set @currLen = @origLen;
set @loopCt = 1;
if isdate(@originalVal) = 1
begin
set @newVal = dateadd(yyyy, 7, @originalVal) - 7
end
else
begin
-- Loop through the characters passed
while @loopCt <= @origLen
begin
set @currLen = datalength(@originalVal);
select @random = convert(int,(((1) - @currLen) * RandomValue + @currLen))
from dbo.vwRandom;
set @newVal = @newVal + substring(@originalVal,@random,1);
set @originalVal = Replace(@originalVal,SUBSTRING(@originalVal,@random,1),'');
set @LoopCt = @LoopCt + 1;
end
end
-- Returns new value
return lower(@newVal);
end
July 24, 2012 at 3:02 pm
It looks like you're trying to put the NewVal varchar(max) variable into a nvarchar(10) field, hence the warning message. Just cast the results to be nvarchar(10).
Why is a SSN field nvarchar() as opposed to varchar()? Just curious.
HTH,
Rob
July 24, 2012 at 3:16 pm
Run this:
SELECT DATALENGTH(CAST('555555555' AS NVARCHAR(10)))
NVARCHAR holding 9 characters is 18 bytes. VARCHAR holding 9 characters is 9 bytes.
Jared
CE - Microsoft
July 24, 2012 at 3:21 pm
Thanks guys ...... Got it to work!!! The nvarchar column is part of a different table and basically this update is used in a stored procedure where multiple columns are updated at one time, I didn't create the tables, I just scramble them.
July 24, 2012 at 3:53 pm
For my own curiosity what is this part of the code doing:
select @random = convert(int,(((1) - @currLen) * RandomValue + @currLen))
from dbo.vwRandom;
Im guessing vwRandom is a view but the question is what is that definition and is it truly random. Unless it has to still be somewhat like a SSN i would look at HASHBYTES and a SHA1 or MD5 hash.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply