January 20, 2014 at 8:22 am
Hello,
I have to update a string in a column with a specific number for each occurrence of the number in the string.
Like 1 = 2, 2 = 3, 3=4 and so on through 9.
I am trying to use a nested replace, but I think I am screwing up the order of operations or something because it will just replace the whole string with whatever is the first replace.
The column is a char(9) and the contents are a 9 digit number like '123456789'
Here is my broken update:
UPDATE [ssntest].[dbo].[usertable]
SET [ssn] = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ssn, '1', '2'), '2', '3'), '3', '4'), '4', '5'), '5', '6'), '6', '7'), '7', '8'), '8', '9'), '9', '0'), '0', '1')
When I run that, it will replace my numbers with all 1's. This would be simple if I didn't have to use specific numbers, but I do.
Can someone point me in the right direction?
January 20, 2014 at 8:40 am
Eight (1/20/2014)
Hello,I have to update a string in a column with a specific number for each occurrence of the number in the string.
Like 1 = 2, 2 = 3, 3=4 and so on through 9.
I am trying to use a nested replace, but I think I am screwing up the order of operations or something because it will just replace the whole string with whatever is the first replace.
The column is a char(9) and the contents are a 9 digit number like '123456789'
Here is my broken update:
UPDATE [ssntest].[dbo].[usertable]
SET [ssn] = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ssn, '1', '2'), '2', '3'), '3', '4'), '4', '5'), '5', '6'), '6', '7'), '7', '8'), '8', '9'), '9', '0'), '0', '1')
When I run that, it will replace my numbers with all 1's. This would be simple if I didn't have to use specific numbers, but I do.
Can someone point me in the right direction?
What you have going on here is a series of nested functions. That means that for each replace what you are passing is the results of the previous replace. That means that no matter what order you use here the value will end up being a series of all the same number.
It seems that what you are trying to do is scramble SSN values. I suspect you could use some math instead of crazy string manipulation like this. Or just maybe just replace a couple of values?
My real concern is why are you storing SSN values in clear text at all? As you are obviously aware this information is sensitive. You should encrypt this data and not store it in clear text. I would focus on encryption instead of trying to obfuscate clear text values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2014 at 8:45 am
Notwithstanding Sean's point about SSNs, you could do something like this. It splits the string into a table of component characters, performs the transformation, and then reconcatenates them.
with numbers(n)as (
-- This is an on-the-fly numbers or tally table
-- There are various ways of generating it,
-- depending how many numbers you want in it
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
, digits as (
SELECT
CASESUBSTRING('12345',n,1)
WHEN '1' THEN '2'
WHEN '2' THEN '3'
WHEN '3' THEN '4'
WHEN '4' THEN '5'
WHEN '5' THEN '1'
END newno,
n oldno
FROM numbers)
SELECT newno + ''
FROM digits
ORDER BY oldno
FOR XML PATH ('')
John
January 20, 2014 at 8:54 am
Sean,
You are 100% correct. The data is encrypted. The issue is complicated, but we are adding an additional layer as a temp measure until the other party can get their test environment set up correctly.
John,
Thanks for that. I will give it a shot.
January 20, 2014 at 9:42 am
You can use replace:
SELECT *
FROM (SELECT ssn = '0123456789') d
CROSS APPLY (
SELECT
ssnAllway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssnhalfway,
'a','3'),'b','9'),'c','1'),'d','6'),'e','2'),'f','8'),'g','0'),'h','7'),'i','4'),'j','5')
FROM (
SELECT
ssnHalfway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssn,
'0','a'),'1','b'),'2','c'),'3','d'),'4','e'),'5','f'),'6','g'),'7','h'),'8','i'),'9','j')
) d
) y
SELECT *
FROM (SELECT ssn = '0123456789') d
CROSS APPLY (
SELECT
ssnAllway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssnhalfway,
'a','3'),'b','9'),'c','1'),'d','6'),'e','2'),'f','8'),'g','0'),'h','7'),'i','4'),'j','5')
FROM (
SELECT
ssnHalfway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssn,
'0','a'),'1','b'),'2','c'),'3','d'),'4','e'),'5','f'),'6','g'),'7','h'),'8','i'),'9','j')
) d
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 21, 2014 at 10:31 am
For anyone wanting to do something similar, this is a function using John's code as the basis.
Thanks for all of your replies
CREATE FUNCTION [dbo].[ScrubNumber] (@RawNumber varchar(100))
RETURNS varchar(100)
AS
BEGIN
declare @retVal varchar(100)
declare @dataLen int
Set @dataLen = Len(rtrim(ltrim(@RawNumber)))
declare @charIndex int
set @charIndex=1
while (@charIndex<=@dataLen)
Begin
Select @retVal = isnull(@retVal,'') + CASE SUBSTRING(@RawNumber,@charIndex,1)
WHEN '1' THEN '2'
WHEN '2' THEN '3'
WHEN '3' THEN '4'
WHEN '4' THEN '5'
WHEN '5' THEN '6'
WHEN '6' THEN '7'
WHEN '7' THEN '8'
WHEN '8' THEN '9'
WHEN '9' THEN '0'
WHEN '0' THEN '1'
END
set @charIndex = @charIndex+1
End
January 21, 2014 at 11:52 am
A scalar defined function as you posted, will giev you performance problems. Try to use an iTVF as explained on this article to make it perform much better.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply