October 22, 2021 at 2:56 pm
My goal is to replace the numbers 1-9 in a column with a different number. For this example the numbers would change to
1=7
2=3
3=9
4=1
5=4
6=2
7=5
8=6
9=8
I tried using the replace function shown below but my problem is a value keeps getting changed as it progresses through each replace. For example, a 1 becomes a 7 which then becomes a 5.
CREATE TABLE dbo.IntChange (NumericValue INT)
INSERT INTO dbo.IntChange
VALUES (15697)
,(876)
,(1452)
,(3374)
,(894)
,(84516)
SELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(NumericValue, 1, 7), 2, 3), 3, 9), 4, 1), 5, 4), 6, 2), 7, 5), 8, 6), 9, 8)
FROM dbo.IntChange
What can do I so that the each number only changes to what it is intended such as the first result in the example provided would be 74285?
October 22, 2021 at 3:15 pm
Something like this?
DECLARE
@input INT = 15697
,@convert VARCHAR(100)
,@position INT = 1
,@result VARCHAR(100)
,@output INT
,@length INT
SELECT @convert = CONVERT(VARCHAR(100),@input)
SELECT @length = LEN(@convert)
WHILE @position <= @length
BEGIN
SELECT @result = ISNULL(@result,'') +
CASE substring(@convert,@position,1)
WHEN '1' THEN '7'
WHEN '2' THEN '3'
WHEN '3' THEN '9'
WHEN '4' THEN '1'
WHEN '5' THEN '4'
WHEN '6' THEN '2'
WHEN '7' THEN '5'
WHEN '8' THEN '6'
WHEN '9' THEN '8'
END
SET @position = @position + 1
END
SELECT @output = CONVERT(INT,@result)
SELECT @input, @output
October 22, 2021 at 3:27 pm
IF OBJECT_ID('tempdb.dbo.#translations') IS NOT NULL
DROP TABLE #translations
CREATE TABLE #translations (
from_char char(1) NOT NULL PRIMARY KEY,
to_char char(1) NOT NULL
)
INSERT INTO #translations VALUES
(1, 7), (2, 3), (3, 9), (4, 1), (5, 4), (6, 2), (7, 5), (8, 6), (9, 8)
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
)
SELECT IC.NumericValue, (SELECT '' + ISNULL(tr.to_char, SUBSTRING(ca1.StringValue, t.number, 1))
FROM cte_tally10K t
LEFT OUTER JOIN #translations tr ON tr.from_char = SUBSTRING(ca1.StringValue, t.number, 1)
WHERE t.number BETWEEN 1 AND LEN(ca1.StringValue)
ORDER BY t.number
FOR XML PATH(''), TYPE) AS AlteredValue
FROM dbo.IntChange IC
CROSS APPLY (
SELECT CAST(IC.NumericValue AS varchar(10)) AS StringValue
) AS ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2021 at 4:04 pm
Well you could do two sets of replaces, so something like replace all the numbers with a letter first then replace all the letters with the new number 🙂
October 22, 2021 at 7:04 pm
This looks like it might be used for the old and totally unsafe hack for "encrypting" SSNs and other critical numeric information. If it is, STOP! Do it correctly with real encryption and a "salt".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2021 at 11:35 am
Well you could do two sets of replaces, so something like replace all the numbers with a letter first then replace all the letters with the new number 🙂
I have a version of this. It works with the two discrete sets (1,7,5,4) and (2,3,9,8,6) from the example provided and uses a VARCHAR method rather than INT.
DROP TABLE IF EXISTS #IntChange;
CREATE TABLE #IntChange
(
NumericValue VARCHAR(20)
);
INSERT #IntChange
(
NumericValue
)
VALUES
('15697')
,('876')
,('1452')
,('3374')
,('894')
,('84516');
SELECT *
FROM #IntChange ic;
SELECT ic.NumericValue
,calc1.Result1
,calc2.Result2
FROM #IntChange ic
CROSS APPLY
(
SELECT Result1 = REPLACE(
REPLACE(
(REPLACE(REPLACE(REPLACE(ic.NumericValue, '1', 'Z'), '4', '1'), '5', '5'))
,'7'
,'5'
)
,'Z'
,'7'
)
) calc1
CROSS APPLY
(
SELECT Result2 = REPLACE(
REPLACE(
REPLACE(
(REPLACE(
REPLACE(REPLACE(calc1.Result1, '2', 'Z'), '6', '2')
,'8'
,'6'
)
)
,'9'
,'8'
)
,'3'
,'9'
)
,'Z'
,'3'
)
) calc2;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 23, 2021 at 12:21 pm
This is one of those gray areas... or idk maybe someone can explain why this actually works. For reasons unknown, afaik when the REPLACE function is used in the righthand side of a string equation in a scalar function then the search and replace terms become table driven. No nested REPLACE functions needed. If someone could point to the documentation which describes this behavior I would appreciate it because I've looked before. Since the function doesn't reference a physical table it can be created WITH SCHEMABINDING. Something like this
drop table if exists #IntChange;
go
create table #IntChange (
NumericValue INT);
insert into #IntChange values
(15697)
,(876)
,(1452)
,(3374)
,(894)
,(84516);
drop function if exists dbo.test_scalar_string_replace;
go
create function dbo.test_scalar_string_replace(
@stringvarchar(max))
returns varchar(max) with schemabinding as
begin
select @string=replace(@string, v.orig, v.repl)
from (values ('1', '7'), ('2', '3'),
('3', '9'), ('4', '1'),
('5', '4'), ('6', '2'),
('7', '5'), ('8', '6'),
('9', '8')) v(orig, repl);
return @string;
end
go
select dbo.test_scalar_string_replace(NumericValue) Conv_Numeric
from
#IntChange;
Conv_Numeric
54285
652
5148
8851
681
61452
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 23, 2021 at 2:20 pm
Hold a sec, the code above is making multiple replacements of the same character. 7 replaces 1 then 5 replaces 7. The reason seems to be because the order of the values in the virtual table actually sets the precedence for the replacements. [Edit] I wiped out the code that here because it doesn't work either
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 23, 2021 at 4:29 pm
The REPLACE method maybe is not appropriate here but I still would like to know how that works. Anyway, given this is version 2016 maybe something like this
select ic.NumericValue,
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
from #IntChange ic
group by ic.NumericValue
order by ic.NumericValue;
From this article using tally function dbo.fnTally
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
Seems correct now
NumericValue xml_string_agg
876 652
894 681
1452 7143
3374 9951
15697 74285
84516 61472
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 23, 2021 at 5:44 pm
My goal is to replace the numbers 1-9 in a column with a different number. For this example the numbers would change to
1=7
2=3
3=9
4=1
5=4
6=2
7=5
8=6
9=8
I tried using the replace function shown below but my problem is a value keeps getting changed as it progresses through each replace. For example, a 1 becomes a 7 which then becomes a 5.
CREATE TABLE dbo.IntChange (NumericValue INT)
INSERT INTO dbo.IntChange
VALUES (15697)
,(876)
,(1452)
,(3374)
,(894)
,(84516)
SELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(NumericValue, 1, 7), 2, 3), 3, 9), 4, 1), 5, 4), 6, 2), 7, 5), 8, 6), 9, 8)
FROM dbo.IntChangeWhat can do I so that the each number only changes to what it is intended such as the first result in the example provided would be 74285?
OK, Ron... your turn. What are you going to use this for?
I ask because the only times I've seen someone request this exact same thing is to make a little homegrown but easily defeated obfuscation of SSNs. What are YOU intending to use if for?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2021 at 12:59 pm
maybe something like this
select ic.NumericValue,
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
from #IntChange ic
group by ic.NumericValue
order by ic.NumericValue;
The outer GROUP BY bothers me. It's not really necessary. This code is preferable imo
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'), ('0', '0'))
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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 3:09 pm
Steve Collins wrote:maybe something like this
select ic.NumericValue,
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
from #IntChange ic
group by ic.NumericValue
order by ic.NumericValue;The outer GROUP BY bothers me. It's not really necessary. This code is preferable imo
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;
Is that code fundamentally different than the first query I posted using the same method?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 25, 2021 at 3:51 pm
Is that code fundamentally different than the first query I posted using the same method?
No temp table
No Cartesian product without row goal, i.e. SELECT TOP(n)
No LEFT JOIN
No ISNULL
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 25, 2021 at 4:01 pm
ScottPletcher wrote:Is that code fundamentally different than the first query I posted using the same method?
No temp table
No Cartesian product without row goal, i.e. SELECT TOP(n)
No LEFT JOIN
No ISNULL
The table table is optional, of course.
The last 3 are meaningless performance-wise.
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.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 25, 2021 at 4:44 pm
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply