August 19, 2015 at 9:11 am
All,
I have a situation where there are account numbers with alpha and numeric characters. I am looking for a way to create a new account number by removing the alpha characters. There are several exceptions which I need to make and this is the part I am stuck on.
The majority of the accounts will be stripped of the alpha characters. This is achieved and you'll see this in the test script below.
Then I need to make exceptions and map them to new numbers entirely. Below is test script and you'll see there are a few accounts such as 5062A, 5062B, 5062C, in an exception list. These accounts need to be transformed to specific numbers. For instance 5062A = 7000, 5062B = 7001, 5062C = 7002.
Finally there is a record I don't want returned in the result because it is a duplicate. In my example this is account Z5055. The account numbered 5055 is the same details wise as Z5055.
My desired result would look like the below example. Notice Z5055 is not included and 3 accounts are transformed to 7000,7001,7002.
AccountNum New Account Num
5055 5055
5056 5056
5057 5057
5058 5058
AB5059 5059
5060DD 5060
5022 5022
5061 5061
5062A 7000
5062B 7001
5062C 7002
Below is test data and a function for stripping the alpha char's.
use tempdb
go
/****************************
drop table #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
--create the function
--*****YOU WILL NEED to change the [YourDatabase] qualifier*****
CREATE FUNCTION [YourDatabase].[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 [YourDatabase].[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
I appreciate any advice you will provide.
August 19, 2015 at 11:00 am
CREATE TABLE #AccountNum_Custom_Handling (
AccountNum varchar(50) NOT NULL PRIMARY KEY,
action_to_take char(1) NOT NULL
CHECK(action_to_take IN ('D', 'S')), --'D'=delete/drop from result; 'S'=substitute another accountnum
AccountNum_Substitution varchar(50) NULL
)
INSERT INTO #AccountNum_Custom_Handling
SELECT '5062A', 'S', '7000' UNION ALL
SELECT '5062B', 'S', '7001' UNION ALL
SELECT '5062C', 'S', '7002' UNION ALL
SELECT 'Z5055', 'D', NULL
SELECT
vm.AccountNum AS OriginalAccountNum,
ISNULL(ach.AccountNum_Substitution, [dbo].[DelAlphaChar](vm.AccountNum)) AS NewAccountNum
FROM #VendorMaster vm
LEFT OUTER JOIN #AccountNum_Custom_Handling ach ON
ach.AccountNum = vm.AccountNum
WHERE
(ach.action_to_take IS NULL OR ach.action_to_take <> 'D')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2015 at 11:25 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply