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
,(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.
,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))
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.