# Replace Numbers

• 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.IntChangeVALUES (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?

• Something like this?

`DECLARE  @input INT = 15697,@convert VARCHAR(100),@position INT = 1,@result VARCHAR(100),@output INT,@length INTSELECT @convert = CONVERT(VARCHAR(100),@input)SELECT @length = LEN(@convert)WHILE @position <= @lengthBEGINSELECT @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'ENDSET @position = @position + 1ENDSELECT @output = CONVERT(INT,@result)SELECT @input, @output`
• `IF OBJECT_ID('tempdb.dbo.#translations') IS NOT NULL    DROP TABLE #translationsCREATE 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);WITHcte_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 AlteredValueFROM dbo.IntChange ICCROSS APPLY (    SELECT CAST(IC.NumericValue AS varchar(10)) AS StringValue) AS ca1`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• 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 🙂

• 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• ZZartin wrote:

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.Result2FROM #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;`

If you aren't happy single, you won't be happy in a relationship.

Remember, happiness comes from guitars, not relationships.

• 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;gocreate table #IntChange (  NumericValue INT);insert into #IntChange values(15697),(876),(1452),(3374),(894),(84516);drop function if exists dbo.test_scalar_string_replace;gocreate 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;endgoselect dbo.test_scalar_string_replace(NumericValue) Conv_Numericfrom #IntChange;`
`Conv_Numeric542856525148885168161452`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• 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.   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

• 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_aggfrom #IntChange icgroup by ic.NumericValueorder by ic.NumericValue;`

`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_agg876             652894             6811452            71433374            995115697           7428584516           61472`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• RonMexico wrote:

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.IntChangeVALUES (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?

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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_aggfrom #IntChange icgroup by ic.NumericValueorder 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.stringfrom #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

• Steve Collins wrote:

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_aggfrom #IntChange icgroup by ic.NumericValueorder 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.stringfrom #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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• 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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Steve Collins wrote:

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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• 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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 1 through 15 (of 39 total)