Replace Numbers
-
My bad. Apparently I did not submit my post last week thanking Ant-Green for his solution and assuring this is not for SSNs or any kind of PII data. There have been many good solutions provided since then.
- RonMexico wrote:
My bad. Apparently I did not submit my post last week thanking Ant-Green for his solution and assuring this is not for SSNs or any kind of PII data. There have been many good solutions provided since then.
In that case, thanks for the feedback and the readily consumable data, Ron.
Since we're working with integers, let's avoid conversions between INT and character based datatypes, implicitly or otherwise. This is a bit of splitting the number using integer math. I also made sure that we didn't bump our heads on the largest value for integers and I kept the output as numeric (BIGINT to handle the larger 10 digit numbers that exceed the limits of INT after conversion).
Here's your readily consumable info with a couple of minor changes and some additions.
--===== Create and populate the test table.
-- Always use temp tables for this so you
-- don't drop an existing real table
DROP TABLE IF EXISTS #IntChange
CREATE TABLE #IntChange (NumericValue INT)
;
INSERT INTO #IntChange
VALUES (15697) --74285
,(876)
,(1452)
,(3374)
,(894)
,(84516)
,(0) --<---<<< Added
,(101928370) --<---<<< Added
,(2147483647) --<---<<< Added
;Here's the "numeric splitter" version. Note that I have not checked to see if it handles negative numbers. You can get the fnTally function from the similarly named link in my signature line below.
SELECT NumericValue
,NewValue = CONVERT(BIGINT,SUM(CHOOSE(NumericValue/p.Power10%10+1,0.0,7.0,3.0,9.0,1.0,4.0,2.0,5.0,6.0,8.0)*p.Power10))
FROM #IntChange
CROSS APPLY (VALUES(CONVERT(INT,ISNULL(LOG10(NULLIF(NumericValue,0)),0)+1)))d(Digits)
CROSS APPLY dbo.fnTally(0,Digits-1)t
CROSS APPLY (VALUES(POWER(10,t.N)))p(Power10)
GROUP BY NumericValue
;Here's the result set using your test data.
Despite what looks like complexity, it runs more that twice as fast than the SUBSTRING versions and it returns a numeric column although it's necessarily a BIGINT. At least one of the solutions prior to this returns an XML column, which is also a LOB, which also slows things down.
--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) -
Why not get rid of FLOAT as soon as possible by CAST'ing POWER to BIGINT ?
select NumericValue, conv_bigint.num
from #IntChange ic
cross apply (select sum(conv.num)
from dbo.fnTally(0,isnull(log10(nullif(ic.NumericValue,0)),0)) t
cross apply (values(cast(power(10,t.n) as bigint))) p(power10)
cross apply (values (choose(ic.NumericValue/p.power10%10+1,0,7,3,9,1,4,2,5,6,8)*p.power10))
conv(num)) conv_bigint(num);Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
Why not get rid of FLOAT as soon as possible by CAST'ing POWER to BIGINT ?
select NumericValue, conv_bigint.num
from #IntChange ic
cross apply (select sum(conv.num)
from dbo.fnTally(0,isnull(log10(nullif(ic.NumericValue,0)),0)) t
cross apply (values(cast(power(10,t.n) as bigint))) p(power10)
cross apply (values (choose(ic.NumericValue/p.power10%10+1,0,7,3,9,1,4,2,5,6,8)*p.power10))
conv(num)) conv_bigint(num);That works fine and makes the code more readable, to boot. My only defense for missing it is too many late nights and I was trying not to drool on my keyboard. 😀
--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) -
A numbers splitter is a really nice solution. I thought the question was already answered. What else could be done with a numbers splitter?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Steve Collins wrote:
A numbers splitter is a really nice solution. I thought the question was already answered. What else could be done with a numbers splitter?
Converting Integers that look like dates into dates for 1. I use part of a "numbers splitter" to figure out a "sample rate" to guarantee that the number of plot points will always be less than 100 thousand for my IndexDNA™ stored procedure so I can easily plot them on a particular "scatter" plot spreadsheet that I use to graphically display what an index looks like. I've also used it to randomly generate upper-alpha/lower-alpha/numeric/symbol passwords that won't spell out any swear words by mistake without the benefit of an "nope... bad word... try again" table of words (still, not a good idea... use part of GUID instead) and for "base converters". A nd, a simple form of "numbers splitter" can be use for creating "columns" in output either by row/column order or column/row order, like what might appear in the white pages phone book (remember those?), and index in the back of a book, etc.
--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) -
Great insights as always Jeff. Ha, yes I do remember those days. No spring chicken here. The first number in my age is a 5.
3 ways to convert an integer to a date
drop table if exists #DtChange;
go
create table #DtChange (
DtAsInt int not null);
insert into #DtChange(DtAsInt) values
(20210101)
,(20201231)
,(20200229)
,(14920101);
select datefromparts(DtAsInt/10000, DtAsInt/100%100, DtAsInt%100),
cast(stuff(stuff(DtAsInt, 7, 0, '-'), 5, 0, '-') as date),
cast(cast(DtAsInt as char(8)) as date)
from #DtChange dc;The first is the preferred answer imo 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
This can be done easily using the Translate() function:
If Object_id('tempdb..#IntChange') is not null Drop table #IntChange;
CREATE TABLE #IntChange (NumericValue INT);
INSERT INTO #IntChange
VALUES (15697) --74285
,(876)
,(1452)
,(3374)
,(894)
,(84516)
,(0) --<---<<< Added
,(101928370) --<---<<< Added
;
Update #IntChange set NumericValue = Translate(NumericValue, '123456789','739142568');If you're using SQL Server 2016 or earlier (as per the example in this discussion) you need to create a user defined function dbo.Translate, since the inbuilt TRANSLATE function is only in SQL Server 2017 or later. Using dbo.Translate the update code becomes:
Update #IntChange set NumericValue = dbo.Translate(NumericValue, '123456789','739142568');
I will post the code of dbo.Translate for SQL Server 2016.
-
Create function [dbo].[Translate](
@inputString varchar(max),
@characters varchar(1000),
@translations varchar(1000)
) returns varchar(max)
/*
Equivalent to SQL Server Translate() function which is in SQL Server 2017 and later.
*/
as begin
declare @i integer = 1;
declare @ch varchar(1);
declare @returns varchar(max) = '';
if @inputString is not null and @characters is not null and @translations is not null
begin
while @i <= Datalength(@inputString)
begin
set @ch = Substring(@inputString, @i, 1);
set @returns = @returns +
case
when Charindex(@ch, @characters) >= 1 then Substring(@translations, Charindex(@ch, @characters), 1)
else @ch
end;
set @i = @i + 1;
end;
end;
return @returns;
end;
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply