Scots solution requires a mapping, mine is just assigning new values.
here's my version, the core is this part at the end:
;With
TheData
AS
(
SELECT
[dbo].[DelAlphaChar](vm.AccountNum) As CleanedNumber,
row_number() OVER (Partition By [dbo].[DelAlphaChar](vm.AccountNum) ORDER BY AccountNum) AS RW, *
From #VendorMaster as vm
),
AllNewValues
AS
(SELECT row_number() OVER (ORDER BY CleanedNumber) + 6999 As NewNumber,*
FROM TheData
WHERE RW > 1
)
SELECT CleanedNumber,RW,AccountNum,Name FROM TheData WHERE RW = 1
UNION ALL
SELECT NewNumber,RW,AccountNum,Name FROM AllNewValues
the full code:
use tempdb
go
/****************************
drop table #VendorMaster
***************************/
IF OBJECT_ID('tempdb.[dbo].[#VendorMaster]') IS NOT NULL
DROP TABLE [dbo].[#VendorMaster]
create table #VendorMaster
(
AccountNumvarchar (50)not null
, Namevarchar (50)not null
)
--insert test data
insert into #VendorMaster
(
AccountNum,Name
)
Values
('5055','Joes Supplies')
,('Z5055','Joes Supplies')
,('5056','Janes Stuff')
,('5057','Big Money Warehouse')
,('5058','Best Wholesale')
,('AB5059','Got Plumbing')
,('5060DD','Things We Got!')
,('5022','Robot Supply Corp')
,('5061','Cable Everywhere')
,('5062A','Dust Collecting NJ')
,('5062B','Dust Collecting NY')
,('5062C','Dust Collecting CA')
--review data
select * from #VendorMaster
IF OBJECT_ID('[dbo].[DelAlphaChar]') IS NOT NULL
DROP FUNCTION [dbo].[DelAlphaChar]
GO
--create the function
--*****YOU WILL NEED to change the [YourDatabase] qualifier*****
GO
CREATE FUNCTION [dbo].[DelAlphaChar](@InputString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@InputString)>0
SET @InputString = STUFF(@InputString,PATINDEX('%[^0-9]%',@InputString),1,'')
RETURN @InputString
END
GO
--Build on this result set
--This is where I haven't derived at a clean way to achieve the desired result.
--*****YOU WILL NEED to change the [YourDatabase] qualifier*****
select AccountNum
,(Select [dbo].[DelAlphaChar](vm.AccountNum)
From #VendorMaster as vm
Where vm.AccountNum not in('Z5055','5062A','5062B','5062C') and vm.AccountNum = #VendorMaster.AccountNum) as NewAccountNum
from #VendorMaster
;With
TheData
AS
(
SELECT
[dbo].[DelAlphaChar](vm.AccountNum) As CleanedNumber,
row_number() OVER (Partition By [dbo].[DelAlphaChar](vm.AccountNum) ORDER BY AccountNum) AS RW, *
From #VendorMaster as vm
),
AllNewValues
AS
(SELECT row_number() OVER (ORDER BY CleanedNumber) + 6999 As NewNumber,*
FROM TheData
WHERE RW > 1
)
SELECT CleanedNumber,RW,AccountNum,Name FROM TheData WHERE RW = 1
UNION ALL
SELECT NewNumber,RW,AccountNum,Name FROM AllNewValues
Lowell