October 25, 2021 at 5:16 pm
ScottPletcher wrote:Your code also has zeros just disappear completely from your results. It's never explicitly stated that zeros can't appear in these numbers. Just in case they do, I think it's better leave them as zero rather than just have them disappear.
Or insert ('0', '0') in the temp/virtual table
Bit misleading, since you're not actually replacing 0. I'd rather leave out what's not being replaced.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 5:30 pm
You feel misled? It's an axiomatic statement of equivalency
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 5:39 pm
I do.
Similarly, if, say, I were substituting letters l and 0 to prevent ambiguity, I'd simply replace just those, not 'A' with 'A', 'B' with 'B', etc.
What about a potential negative sign? You gonna add a phony replacement in for that too?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 5:44 pm
maybe something like this
select ic.NumericValue, xml_string_agg.string
from #IntChange ic
cross apply (select stuff((select ''+v.repl
from #IntChange icc
cross apply dbo.fnTally(1, len(icc.NumericValue)) fn
cross apply (values (substring(cast(icc.NumericValue as varchar(20)), fn.n, 1))) c(chr)
join (values ('4', '1'), ('6', '2'),
('2', '3'), ('5', '4'),
('7', '5'), ('8', '6'),
('1', '7'), ('9', '8'),
('3', '9')) v(orig, repl) on c.chr=v.orig
where icc.NumericValue=ic.NumericValue
order by fn.n
for xml path('')), 1, 0, '')) xml_string_agg(string)
order by ic.NumericValue;
I also don't think it's necessary to re-read from the original table in the concat query. I skipped that in my code because I didn't think it was necessary.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 5:58 pm
In general, I say replace only those chars that you the algorithm requires you to replace, leave all others alone.
Otherwise, say 6 months from now, they decide to convert it to varchar and sometimes include dashes in the value. Not sure why, they just need that.
If the code just arbitrarily strips out any char that won't get replaced, that bug will seem "impossible" later when a problem comes up. How could data just disappear from the column, you'd think?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 6:09 pm
While you guys are bantering, I hope it's your SSNs that the OP is going to obfuscate with this easily broken method. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2021 at 6:20 pm
I try not to mastermind someone else's needs for code. If they confirm it's ssns or something else critical, then that needs to change. Nothing about the ssn should be visible to 99.9% of employees anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 6:24 pm
Wow all that. Ok maybe something like this
select IcC.NumericValue,
stuff((select ''+v.repl
from dbo.fnTally(1, len(icc.NumericValue)) fn
cross apply (values (substring(cast(icc.NumericValue as varchar(20)), fn.n, 1))) c(chr)
join (values ('4', '1'), ('6', '2'),
('2', '3'), ('5', '4'),
('7', '5'), ('8', '6'),
('1', '7'), ('9', '8'),
('3', '9'), ('0', '0'))
v(orig, repl) on c.chr=v.orig
order by fn.n
for xml path('')), 1, 0, '') AlteredValue
from #IntChange icc;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 6:33 pm
Wow all that. Ok maybe something like this
select IcC.NumericValue,
stuff((select ''+v.repl
from dbo.fnTally(1, len(icc.NumericValue)) fn
cross apply (values (substring(cast(icc.NumericValue as varchar(20)), fn.n, 1))) c(chr)
join (values ('4', '1'), ('6', '2'),
('2', '3'), ('5', '4'),
('7', '5'), ('8', '6'),
('1', '7'), ('9', '8'),
('3', '9'), ('0', '0'))
v(orig, repl) on c.chr=v.orig
order by fn.n
for xml path('')), 1, 0, '') AlteredValue
from #IntChange icc;
Still arbitrarily stripping out any negative sign (or a dash deliberately included later). Again, in my view it's fatally flawed to arbitrarily strip every char that doesn't match one that needs replaced.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 6:52 pm
To review, the table definition provided by the OP
CREATE TABLE dbo.IntChange (NumericValue INT)
INSERT INTO dbo.IntChange
VALUES (15697)
,(876)
,(1452)
,(3374)
,(894)
,(84516)
You have some reason to believe the future universe of possible inputs might contain a character other than 0-9? Do they have flying cars in the future too? At this point I'm content to wait for the OP to mark the code they choose to be most helpful. Also, I agree with Jeff this seems suspiciously like flimsy data masking. If so there are definitely better ways to accomplish that goal
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 7:07 pm
What about a potential negative sign? You gonna add a phony replacement in for that too?
Good idea! Yes, I added ('-', '-') and it works with negative integers now too
select ic.NumericValue,
stuff((select ''+v.repl
from dbo.fnTally(1, len(ic.NumericValue)) fn
cross apply (values (substring(cast(ic.NumericValue as varchar(20)), fn.n, 1))) c(chr)
join (values ('4', '1'), ('6', '2'),
('2', '3'), ('5', '4'),
('7', '5'), ('8', '6'),
('1', '7'), ('9', '8'),
('3', '9'), ('0', '0'),
('-', '-')) v(orig, repl) on c.chr=v.orig
order by fn.n
for xml path('')), 1, 0, '') AlteredValue
from #IntChange ic
order by ic.NumericValue;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 8:50 pm
To review, the table definition provided by the OP
CREATE TABLE dbo.IntChange (NumericValue INT)
INSERT INTO dbo.IntChange
VALUES (15697)
,(876)
,(1452)
,(3374)
,(894)
,(84516)You have some reason to believe the future universe of possible inputs might contain a character other than 0-9? Do they have flying cars in the future too? At this point I'm content to wait for the OP to mark the code they choose to be most helpful. Also, I agree with Jeff this seems suspiciously like flimsy data masking. If so there are definitely better ways to accomplish that goal
Just general IT knowledge that things often change over time. Those values certainly don't like SSNs and seem deliberately not to contain zeros (which does not match SSNs or cc #s). So it's some other type of value. Which means they might choose to use char/varchar in the future to store it perhaps with added chars. Just no way I'm not going to write code that arbitrarily removes chars from a value just because I decide "it will never change in the future". Just way too risky to do that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 25, 2021 at 9:52 pm
What about those flying cars? Your code converts the 'NumericValue' column into VARCHAR(10) which is only ok as long as the integer is positive. My code converted to VARCHAR(20) because it allowed for the largest positive and negative BIGINT.
declare @biggest_negative_int int=-2147483647;
select len(@biggest_negative_int) as len_big_neg_int,
cast(@biggest_negative_int AS varchar(10)) AS String10Value,
cast(@biggest_negative_int AS varchar(11)) AS String11Value;
Updated to CAST to VARCHAR(11)
select ic.NumericValue,
stuff((select ''+v.repl
from dbo.fnTally(1, len(ic.NumericValue)) fn
cross apply (values (substring(cast(ic.NumericValue as varchar(11)), fn.n, 1))) c(chr)
join (values ('4', '1'), ('6', '2'),
('2', '3'), ('5', '4'),
('7', '5'), ('8', '6'),
('1', '7'), ('9', '8'),
('3', '9'), ('0', '0'),
('-', '-'))
v(orig, repl) on c.chr=v.orig
order by fn.n
for xml path('')), 1, 0, '') AlteredValue
from #IntChange ic
order by ic.NumericValue;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 26, 2021 at 12:28 am
Jeff Moden wrote:While you guys are bantering, I hope it's your SSNs that the OP is going to obfuscate with this easily broken method. 😀
Don't worry I'm sure it's just for credit card number or something else unimportant.
😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply