September 2, 2009 at 7:06 pm
Hi,
Need advise on what the best solution would be for the following :-
I would like to create a stored procedure to format the Address field in a table which includes approximately 700,000 -->1,000,000+ records.
Address details in this table are scattered over many columns ( Attached--- i have given a simplistic example of the table.) highlighted in green are the address details. Also keep in mind there are atleast 14 different variations when it comes to address formats ie. addresses that start with/include U --> Unit, A-->Apartment , P-->PO Box , S--> Shop etc. i need to create a procedure that consolidates all these columns into an Address1 + Address2 fields-highlighted in blue.
An example of Rules that govern addresses with respect to each format type:-
Normal Addresses - Street_type = ' ' and Street_Number 1 > ' '
Unit Addresses - Street_type = ' U' and Street_Number 1 > ' '
Villa Addresses -Street_type = ' V' and Street_Number1 > ' '
Post Office BOX Addresses -Street_type = ' P' and Street_Number 1 = 'O'
.....
.... etc
What would the best way to design a SP which handles this?
September 2, 2009 at 8:19 pm
The best way I can think of to handle this is with a CASE expression to build the address1 column. Instead of doing this in a stored procedure, you should consider adding address1 as a computed column to the table, or as a computed (non-persisted) column in a view of the table. That makes the solution available to all future queries.
The following code illustrates case statements for normal addresses and Units. You should be able to modify and expand it to solve your problem. Let me know if you have any questions.
declare @sample table (customer_id int, street_type char(1), street_type_number varchar(10), street_number1 varchar(10), street_name varchar(30))
--
insert into @sample
select 101,null, null, 33, 'Ascote Avenue' union all
select 102,'U', 2, 210, 'Woopi Street'
--
select * from @sample
--
select customer_id, case when Street_type is null then ISNULL(street_number1+' ','')+street_name
when Street_type = 'U' then 'Unit '+ ISNULL(street_type_number+'/','')+ ISNULL(street_number1+' ',' ')+Street_name
else 'Undefined'
end as Address1
from @sample
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 3, 2009 at 12:36 am
Appreciate that Bob, it looks good (so obvious :-))
But the reason i am after a stored procedure is that I have SSIS package which each fortnightly exports+tranforms+performs other tasks on 2 text files after a DB refresh.
In this pacakge i would simply like to call a SP via the TSQL task, which performs basic formatting and allocates addresses to address1, address2, suburb, state, postcode field in the MASTER Table--- as i stated in my original question this info is spread across many columns. As such I need to create a rule base and assign each rule to a variable.
So i was thinking, create a store procedure where, first I need to declare variables and assign them rules.
Eg:-
@ Target_Table
Variable_Normal_Addr ?Normal Addresses - Street_type = ' ' and Street_Number 1 > ' '
Variable_Unit_Addr ?Unit Addresses - Street_type = ' U' and Street_Number 1 > ' '
Variable_Villa_Addr ?Villa Addresses -Street_type = ' V' and Street_Number1 > ' '
Variable_PO_Addr ?Post Office BOX Addresses -Street_type = ' P' and Street_Number 1 = 'O'
THEN perform the updates in the @ Target_Table with the aid of conditional statements (case/if else ?) which do all the updates ….
Am interested in the validity of such solution, are there better alternatives, more importantly how to translate a query such as yours into a stored procedure?
Cheers
baz
September 3, 2009 at 6:45 am
Is your intent to store the resulting concatenated address in the target table or to store the various components? I always prefer storing the components, because it is much easier to string them together than to pull them apart. It also gives you the option to order and group by the components, for example street number within street. If your intent is to store the components, I would definitely consider altering the table to have a computed, non-persisted column that represents the complete address field. I can give you an example of this if you need one. Then the only thing your loading job has to do is populate the component columns and the "virtual" column for the concatenated address will be available for all future queries.
As such I need to create a rule base and assign each rule to a variable
I am a little confused about your question about how to translate a query into a stored procedure, and by the above statement. Please don't be offended when I ask have you ever written a stored procedure before? Pretty much any query can be wrapped in a stored procedure. There is no need to first put all your values into variables. Putting all of the rows into the target table can be done with a single query in the form
INSERT INTO TargetTable
SELECT CustomerID, CASE WHEN etc as Street_Adress1
FROM SourceTable
Excuse me if I'm misunderstanding something.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 3, 2009 at 6:10 pm
Naah mate your spot on.
My intent is to "store the resulting concatenated address in the target table " in address1 , address2 fields.
Also if it's not too much of an inconvenience i would appreciate this example, Am interested to know how yop would do that? :-
"If your intent is to store the components, I would definitely consider altering the table to have a calculated non-persisted column that represents the complete address field. I can give you an example of this if you need one. "
Also i am definitely not offended by you question. "Please don't be offended when I ask have you ever written a stored procedure before? " Response to that is essentially NO. Well actually i have written one stored procedurea few weeks ago to creating an XTAB using SQL pivot operator. So this would be my second if i complete it, SO am a NOVICE and am trying to learn this as quick and as best as i can. Thus again appreciate ur valubale time/knowledge.
Ta
Baz
September 3, 2009 at 6:55 pm
Okay... one example at a time. Here I'm going to add a computed column. (I'm sorry for saying "calculated" in previous posts. I'm going to go back and change that. Computed column is the correct term, so you can read more in books online.) Non-persisted means that no additional data is stored in the table. But the concatenation is done automatically whenever the [address1] column is requested.
if object_id(N'tempdb..#sample') is not null drop table #sample
Create table #sample (customer_id int, street_type char(1), street_type_number varchar(10),
street_number1 varchar(10), street_name varchar(30))
--
insert into #sample
select 101,null, null, 33, 'Ascote Avenue' union all
select 102,'U', 2, 210, 'Woopi Street'
--
select * from #sample
-- add a computed column
ALTER TABLE #sample
ADD StreetAddress1 AS (
case when Street_type is null then ISNULL(street_number1+' ','')+street_name
when Street_type = 'U' then 'Unit '+ ISNULL(street_type_number+'/','')+ ISNULL(street_number1+' ',' ')+Street_name
else 'Undefined'
end
)
-- see the result of having a computed column
select * from #sample -- same query, but....
There are a couple of reasons I prefer this, and keep urging you to do it this way. First, because the base table has component columns, we can build indexes on street or address type. With indexes, if you ever want to search for addresses on a particular street, it would run MUCH faster to say
WHERE street_name like 'Woopi%'
instead of
WHERE address1 like '%Woopi%'
Yes, yes, I know that full text indexes are available now, but wait there's more. You could also GROUP BY or ORDER BY street_name to get statistics. You can't do that with '%Woopi%'. Then there is the issue of potential changes in the future. Maybe the way you put together a type of address changes, or maybe an address correction is necessary. (They told you it was a villa, but then tell you it's a unit.)
Using a computed column any updates to the data, or any changes in the method of concatenation, are immediately reflected in all queries which reference the computed column. Storing the concatenated address, if a method changes you would have to go back through all the existing addresses to parse them and rebuild them.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 3, 2009 at 7:22 pm
Here's the example of a stored procedure that concatenates the addresses and puts them into a target table. Obviously you would use your own code with permanent target table, and whatever changes you want to make to the CASE expression.
CREATE PROCEDURE dbo.SampleInsertConcatenated
AS
BEGIN
SET NOCOUNT ON; -- Keep this line, no matter how you change the query
-- set up sample table and data, and sample target table
-- (using a table variable here instead of permanent table)
declare @sample table
(customer_id int, street_type char(1), street_type_number varchar(10),
street_number1 varchar(10), street_name varchar(30))
declare @target table (customer_id int, address1 varchar(50))
--
insert into @sample
select 101,null, null, 33, 'Ascote Avenue' union all
select 102,'U', 2, 210, 'Woopi Street'
-- insert the concatenated address into a target table
-- (LOOK MA, NO VARIABLES!)
insert into @target
select customer_id, case when Street_type is null then ISNULL(street_number1+' ','')+street_name
when Street_type = 'U' then 'Unit '+ ISNULL(street_type_number+'/','')+ ISNULL(street_number1+' ',' ')+Street_name
else 'Undefined'
end as Address1
from @sample
select * from @target
END
GO
/* test
exec dbo.SampleInsertConcatenated
*/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 3, 2009 at 8:14 pm
I am speechless, Thank you! Thank you! very kind+informative.
More than enough info for me to go on with, again appreciate that.
One last question, i know in this day and age there are numerous resources online to learn all sort of new technologies.
In terms of online material,where would you recommend a beginner who's interested in DB development (and is in an infany of his career as Java/C# programmer and looking to start from virtually scratch as a DB developer), more specifically at this point i am exploring topics suchs as stored procedures, functions, SSIS + reporting services.
Any tips and advise.
Thanks again.
September 3, 2009 at 8:33 pm
No problem, Baz. Glad I could help.
Fortunately, there is a ton of material online. SSC has lots of articles published here, MSDN is obviously a good resource, and all kinds of people have blogs. Google can be your best friend, as can the search function in SQL Services Management Studio. Unfortunately there is a TON of material online. I can't really say which are best for beginners and you covered a lot of ground in your areas of interest.
I've got an idea though: Start another thread. Call it "What are best links for beginners?" or something and ask the question again. You will get other people's input to get you started. The best advice I can give you is to just dive in. I read articles here in SSC and I read threads on topics just because the question sounds interesting. I spend at least an hour a day just browsing online, and trying stuff out. I'm still learning and expect that to continue, hopefully, until the day I die.
Good luck to you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply