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

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

    Helpful Links:
    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.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;

    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;
    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

  • 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

  • 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

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

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

    Helpful Links:
    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_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

  • 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_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) 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)

You must be logged in to reply to this topic. Login to reply