June 13, 2018 at 7:13 am
Good day,
Please need your help here :
I have a address field in a table, There is a logic in that field where i need to split it into street(%%S), POBOX(%%X), Postalcode(%%P) & Pvtbag(%%G).
Appreciate your help...Below is the sample data:
--TABLE1
CREATE TABLE [dbo].[Table1](
[ADDRESS] [varchar] (200) NULL,
) ;
-- OUT PUT TABLE, OUTPUT SHOULD LOOK LIKE IN THE TABLE
CREATE TABLE [dbo].[OUTPUTTable](
[STREET] [varchar] (50) NULL,
[POBOX] [int] NULL,
[POSTCODE] [int] NULL,
[PVTBAG] [int] NULL
) ;
GO
INSERT INTO Table1
VALUES ('C/O NANA NOZIPHO DLAMINI;%%X2177;%%P;;;;;;;',
';;;;;;;;;',
'%%X8532;%%P;;;;;;;;',
';;;;;;;;;',
'TOWN SECRETARY DEPARTMENT;;;;;;;;;',
'HAMILTON LODGE 403;;;;;;;;;',
'%%X8532;%%P;;;;;;;;',
'%%X80;%%P;;;;;;;;',
'%%S;;;;;;;;;',
'%%X60666;%%P;%%S;;;;;;;');
GO
INSERT INTO OUTPUTTable
VALUES ('C/O NANA NOZIPHO DLAMINI', 2177,,),
('',,,),
('',8532,,),
('',,,),
('TOWN SECRETARY DEPARTMENT',,,),
('HAMILTON LODGE 403',,,),
('',8532,,),
('',80,,),
('',,,),
('',60666,,) ;
As you noticed above, the source data is not stable, Street cloumn also populated without '%%S'
Thank You..
June 14, 2018 at 4:29 pm
As long as the formatting in the source table is consistent, i.e. always %%S, %%X, %%P, then it comes down to basic substrings. It doesn't matter if any given portion is missing.
While the code may look a bit messy, just need some CHARINDEX(';%%S',ADDRESS,1) etc to work out where each section starts and ends, or if it isn't there at all, then a bit of logic to work out the relevant SUBSTRING start points and lengths.
I started working an this but realized I don't have this much free time, and since it's your job, you can pick it up from here.declare @ADDRESS [varchar] (200)
declare @Street int, @Box int, @PosCode int, @PrvtBag int
set @ADDRESS = 'C/O NANA NOZIPHO DLAMINI;%%X2177;%%P;;;;;;;'
select @Street = CHARINDEX(';%%S',@ADDRESS,1)
select @Box = CHARINDEX(';%%X',@ADDRESS,1)
select @PosCode = CHARINDEX(';%%P',@ADDRESS,1)
select @PrvtBag = CHARINDEX(';%%G',@ADDRESS,1)
Cheers
Leo
Nothing in life is ever so complex that with a little work it can't be made more complex!
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 15, 2018 at 10:00 am
CHARINDEX finds a position in a string. You tell it what you're looking for. SUBSTRING gets partial strings. Combine them to extract the items you need.
If there are strange cases, make a pass with known values, then build cases to handle specifics. If the %s% isn't used, how can you tell it's a street?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply