February 11, 2009 at 6:51 am
First let me preface by saying I am very new at this (less than 6 months) and I am a hardware guy… but my company needed someone to write reports sooo….
I have SQL 2005 on Server 2003. I am using Visual Studio 2005 and deploying the reports to SSRS.
I need some help and advice on how to handle in a field of a table…
For ex, the address field looks like this:
P.O. Box 1234
Main Street 123
And sometimes the address might be 3 lines so would have 2 returns to handle. The address is of varying length.
I have a feeling that I should be looking to create a function but for my immediate needs I think I should just handle this via an Expression…
Any guidance/help would be greatly appreciated…
February 11, 2009 at 7:59 am
Your address pieces should definetely be split into separate columns in your address table. The front end (your report definition) should handle to omit those columns, which are NULL then.
brgds
Philipp Post
brgds
Philipp Post
February 11, 2009 at 8:07 am
Unfortunately, they are not split. I need to split it out there in lies my problem... I do not know how to handle it.
I need 1st part of address to be in one column, the second part in a second column and if there is a 3rd part to address, it will need to be in a third column.
February 11, 2009 at 9:24 am
Could you post the structure of the table you are querying, some sample data, and your current query? See the links in my signature for some help on this.
What I think you should do is "Split" the address in your query, but without structure it is difficult to say for sure, but if you are guaranteed no more than three lines in the address column you could do something like this (gets a bit ugly):
[font="Courier New"]-- this is for test data
DECLARE @addresses TABLE (address_id INT IDENTITY(1,1), address VARCHAR(100))
-- this is an intermediate table
DECLARE @new_addresses TABLE (address_Line_id INT, address_Id INT, address VARCHAR(100))
/* char(10) puts in a Line feed the CHAR function expects and ASCII character code
ASCII Character codes can be found here http://www.asciitable.com/ */
-- create the test data
INSERT INTO @addresses (
address
) SELECT 'P.O. Box 1234' + CHAR(10) + 'Main Street 1234'
/* this cte creates a "temporary numbers view" for use in splitting the addresses
see this article http://www.sqlservercentral.com/articles/TSQL/62867/ for deeper
explanation of all this code except the cte*/
;WITH cteNums AS
(
SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS n
FROM
sys.all_columns AS AC
)
-- insert the split addresses into the intermediate table
INSERT INTO @new_addresses (
address_Line_id,
address_Id,
address
) SELECT
-- this identifies which one comes first
ROW_NUMBER() OVER (ORDER BY address_id, n) AS row_id,
address_id, -- to join on original table
-- splits the long address. You need to pre-pend and append the split character for this to work
SUBSTRING(CHAR(10) + address + CHAR(10), n+1,
CHARINDEX(CHAR(10), CHAR(10) + address + CHAR(10), n + 1) - n - 1) AS address
FROM
-- the cross join provides a row for each row in each table
@addresses CROSS JOIN
cteNums
WHERE
-- this gets the number we need
n < LEN(CHAR(10) + address + CHAR(10)) AND
-- this finds the next split character again make sure you pre-pend and append it
SUBSTRING(CHAR(10) + address + CHAR(10), n, 1) = CHAR(10)
ORDER BY n
/* now we can return the data and assumes we always have 1 address row and up to 3 */
SELECT
A.address_id,
NA1.address AS address_1,
NA2.address AS address_2,
NA3.address AS address_3
FROM
@addresses AS A JOIN
@new_addresses AS NA1 ON
A.address_id = NA1.address_Id AND
NA1.address_Line_id = 1 LEFT JOIN
@new_addresses AS NA2 ON
A.address_id = NA2.address_Id AND
NA2.address_Line_id = 2LEFT JOIN
@new_addresses AS NA3 ON
A.address_id = NA3.address_Id AND
NA3.address_Line_id = 3
[/font]
And yes you can do all this in the Data Source query in SSRS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 11, 2009 at 10:10 am
Thanks for the reply....
Bear with me... read your links to make sure I at least come close to what you need.... 😀
Also, should have mentioned this before... The reason I need it seperated is for a mailing list. This report will be exported to Excel, scrubbed by a user then sent to Post Office to go through there app to do bulk mailing...
My Query so far:
SELECT PartyAddress.company_name, PartyAddress.address_1, PartyAddress.address_1_city, PartyAddress.address_1_state, PartyAddress.address_1_zip
FROM vcase_parties AS PartyName LEFT OUTER JOIN
vlegal_entity AS PartyAddress ON PartyName.entity_sk = PartyAddress.legal_entity_sk
So with this I can get the name of a company, street address, city, state, zip. The problem is that the address field in the table has a return. Instead of separate fields for PO Box, Suite # and/or physical address… it is all in the same field… EX:
Currently looks like:
Company NameAddress City State Zip
ABC Company P.O. Box 123 AnywhereVirginia 12345
123 Main Street
ABC2 Company Health Services AnywhereFlorida 23456
Billing Department
123 Broad Street
What it needs to look like:
Company Name Address1 Address2 Address3 City State Zip
ABC Company P.O. Box 123 123 Main Street Anywhere Virginia 12345
ABC2 Company Health Services Billing Department 123 Broad StreetAnywhere Florida 23456
I have a test DB with data that I basically do a backup of Prod and attach as Test... I am going to play with your above code....
Again...thanks for your help!!!
February 12, 2009 at 7:22 am
Been playing with this... I understand the jest of it but having trouble with passing the table/field into it...
I thought I would just change the
-- create the test data
INSERT INTO @addresses (
address
) SELECT 'P.O. Box 1234' + CHAR(10) + 'Main Street 1234'
to
-- create the test data
INSERT INTO @addresses (
address
) SELECT address_1 --this being the field I need to seperate, changed the Alias from above post
FROM vlegal_entity AS Address
I figured out that I had to use Alias instead of the table name... Got the error identifier could not be bound.... But now I am getting the error:
Msg 8152, Level 16, State 10, Line 11
String or binary data would be truncated.
The statement has been terminated.
What am I doing wrong?
I do have a csv file with data that I can upload if that will help....
February 12, 2009 at 8:37 am
Okay, as I said in my first post:
Could you post the structure of the table you are querying, some sample data, and your current query? See the links in my signature for some help on this.
THe structure of the table means something like:
Create Table addresses
(
id int,
name varchar(50),
address varchar(500),
city varchar(50),
state char(2)
zip varchar(10)
)
The stuff I setup in code was just an example to play with. You do not the @addresses table variable as you HAVE the REAL table with the data. You would need the @new_addresses table variable (with adjusted lengths for the varchar columns) and the code from the CTE on down where you replace @addresses with your table(s)/query. You just need to be sure you have a link back to the original data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 12, 2009 at 8:51 am
My apologies... again I am very new at this.
Thanks for your help
February 13, 2009 at 8:45 am
I just saw this post in another group:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/4584f53be9b7b973#
I have not tested it yet, but it seems this guy has a similar problem and found his way through it. Might be of some help.
brgds
Philipp Post
brgds
Philipp Post
February 14, 2009 at 6:39 am
Paul,
What am I doing wrong?
try this:
CREATE TABLE Pauls_Addresses
(company_code INT NOT NULL PRIMARY KEY,
company_name VARCHAR(30) NOT NULL,
company_address VARCHAR(255) NOT NULL,
company_address1 VARCHAR(30), -- new address line1
company_address2 VARCHAR(30), -- new address line2
company_address3 VARCHAR(30), -- new address line3
city_name VARCHAR(30) NOT NULL,
state_name VARCHAR(30) NOT NULL,
zip_code CHAR(5) NOT NULL);
GO
INSERT INTO Pauls_Addresses (company_code, company_name, company_address, city_name, state_name, zip_code)
VALUES(1, 'ABC Company', 'P.O. Box 123' + CHAR(13) + CHAR(10) + '123 Main Street', 'Anywhere', 'Virginia', '12345');
INSERT INTO Pauls_Addresses (company_code, company_name, company_address, city_name, state_name, zip_code)
VALUES(2, 'ABC Company2', 'Health Services' + CHAR(13) + CHAR(10) + 'Billing Department' + CHAR(13) + CHAR(10) + '123 Broad Street', 'Anywhere', 'Florida', '23456');
-- =============================================
-- Author: Philipp Post
-- Create date: 2009-02-14
-- Description: Splits a CR-LF delimited address
-- into three columns
-- =============================================
CREATE PROCEDURE Split_Address(@company_code INT)
AS
BEGIN
DECLARE @input_string VARCHAR(8000);
DECLARE @delimiter_position INTEGER;
DECLARE @counter INTEGER;
SET @input_string = (SELECT company_address + '~' -- add delimiter at the end
FROM Pauls_Addresses
WHERE company_code = @company_code);
-- make it a delimited list
-- delimiter is the ~
SET @input_string = REPLACE(REPLACE(@input_string, CHAR(13), ''), CHAR(10), '~');
SET @delimiter_position = CHARINDEX('~', @input_string);
SET @counter = 1
WHILE @delimiter_position > 1
BEGIN
UPDATE Pauls_Addresses
SET company_address1 = CASE WHEN @counter = 1 THEN
CAST(LEFT(@input_string, (@delimiter_position - 1)) AS VARCHAR(30))
ELSE company_address1 END,
company_address2 = CASE WHEN @counter = 2 THEN
CAST(LEFT(@input_string, (@delimiter_position - 1)) AS VARCHAR(30))
ELSE company_address2 END,
company_address3 = CASE WHEN @counter = 3 THEN
CAST(LEFT(@input_string, (@delimiter_position - 1)) AS VARCHAR(30))
ELSE company_address3 END
WHERE company_code = @company_code
-- erase the processed part of the input string
SET @input_string = RIGHT(@input_string, LEN(@input_string)- @delimiter_position);
-- assign new delimiter position
SET @delimiter_position = CHARINDEX('~', @input_string);
-- increase address part counter by one
SET @counter = @counter + 1
END;
END;
GO
-- Run this for each address you wish to update
-- this might be in an INSERT or UPDATE trigger when
-- new addresses are entered or within a CURSOR
-- BETTER however: clean up the database/front end design
-- and avoid that this mess re-occurrs ever
--EXEC Split_Address @company_code = 1;
-- CURSOR example
DECLARE @company_code INT;
DECLARE companies_cursor CURSOR FOR
SELECT company_code
FROM Pauls_Addresses
WHERE company_address IS NOT NULL
OPEN companies_cursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM companies_cursor
INTO @company_code;
IF @@FETCH_STATUS < 0
BREAK;
EXEC Split_Address @company_code;
END;
CLOSE companies_cursor;
DEALLOCATE companies_cursor;
-- look at the result
SELECT *
FROM Pauls_Addresses;
brgds
Philipp Post
brgds
Philipp Post
February 16, 2009 at 7:08 am
Thanks Phillip. Will give it a try...
February 19, 2009 at 7:49 am
I am making some progress... I am using the code that Jack put above (thank you Jack)... I am kind of got it running....
First, I am running this against a test DB which is just the Production DB copy called Test. The Table I am pulling the data from has 82 columns of which I am only interested in 4. They are:
address_1 (text, null)
address_1_city (varchar(25), null)
address_1_state(varchar(2), null)
address_1_zip (varchar(10), null)
address_1 column is the problem. It has as many as 3 lines ( with 2 end of line returns).... which I need to seperate out. The other 3 are not an issue.
I am running the code in a new query window in MS SQL Server Management Studio as an Admin.
Hopefully, I covered everything this time.... so I have modified (in bold) the code that Jack posted which is this:
-- this is for test data
DECLARE @addresses TABLE (address_id INT IDENTITY(1,1), address VARCHAR(250))
-- this is an intermediate table
DECLARE @new_addresses TABLE (address_Line_id INT, address_Id INT, address VARCHAR(250))
/* char(10) puts in a Line feed the CHAR function expects and ASCII character code
ASCII Character codes can be found here http://www.asciitable.com/ */
-- create the test data
INSERT INTO @addresses (
address
) SELECT mail.address_1
FROM vlegal_entity AS mail
I am able to insert the field (column) into the table and I can do select * from @address and it returns all 50,000+ address... but...
when I try to run the next part I can only get 1 row returned... I have spent a couple days looking over the code but only moderately understand it....
Any guidance as to what I should look at would be appreciated....
February 19, 2009 at 8:15 am
As I said earlier you DO NOT need the @addresses table as long as you have a primary or unique key in your source table.
Your code would only have to be like this ( REPLACE ADDRESS_ID WITH THE PRIMARY OR UNIQUE KEY OF YOUR TABLE you may
have to change the structure of @new_addresses for this ) :
/* this cte creates a "temporary numbers view" for use in splitting the addresses
see this article http://www.sqlservercentral.com/articles/TSQL/62867/ for deeper
explanation of all this code except the cte*/
;WITH cteNums AS
(
SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS n
FROM
sys.all_columns AS AC
)
-- insert the split addresses into the intermediate table
INSERT INTO @new_addresses (
address_Line_id,
address_Id,
address
) SELECT
-- this identifies which one comes first
ROW_NUMBER() OVER (ORDER BY address_id, n) AS row_id,
address_id, -- to join on original table
-- splits the long address. You need to pre-pend and append the split character for this to work
SUBSTRING(CHAR(10) + address_1 + CHAR(10), n+1,
CHARINDEX(CHAR(10), CHAR(10) + address + CHAR(10), n + 1) - n - 1) AS address
FROM
-- the cross join provides a row for each row in each table
vlegal_entity CROSS JOIN
cteNums
WHERE
-- this gets the number we need
n < LEN(CHAR(10) + address_1 + CHAR(10)) AND
-- this finds the next split character again make sure you pre-pend and append it
SUBSTRING(CHAR(10) + address_1 + CHAR(10), n, 1) = CHAR(10)
ORDER BY n
/* now we can return the data and assumes we always have 1 address row and up to 3 */
SELECT
A.address_id,
NA1.address AS address_1,
NA2.address AS address_2,
NA3.address AS address_3
FROM
vlegal_entity AS A JOIN
@new_addresses AS NA1 ON
A.address_id = NA1.address_Id AND
NA1.address_Line_id = 1 LEFT JOIN
@new_addresses AS NA2 ON
A.address_id = NA2.address_Id AND
NA2.address_Line_id = 2LEFT JOIN
@new_addresses AS NA3 ON
A.address_id = NA3.address_Id AND
NA3.address_Line_id = 3
If you don't have a Primary or Unique Key on the vlegal_entity table then you would use the @addresses table to create the unique key using the identity column I created earlier. You need the Primary/Unique Key in order to join back to it from teh @new_addresses table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 19, 2009 at 9:34 am
The table does have a primary key and this is just one of the tables that I may have to pull an address from but all the tables that I would need to get the address from do have a primary key.
Maybe I should be more clear as to what I thought I should do to handle this and see if this is the best solution or if I should be handling this differently...
I am writing multiple reports to handle mailing lists. for Example:
All Docters
All Courts
All Clients
etc... some of the reports will have multi selects if there is a need to only retrieve regions...
The mail list for the most part are relatively small... prob no more than 10k to 15k.
I was planning on creating a Function to pass just the column (i.e. the address field) into and have it return the 1 to 3 columns back. Since I was writing several reports that would need to 'fix' the address, I figured it would be best handled via a function.
Am I incorrect in this thinking? or Is it better to pass the enitre table. I figured that passing the field would be more efficient than passing the entire table.
February 20, 2009 at 1:27 pm
Am I incorrect in this thinking? or Is it better to pass the enitre table. I figured that passing the field would be more efficient than passing the entire table.
The most efficent would be to fix the address table one time and then the queries would be easy. The table is not designed correctly and you will run into problems over and over again. SQL and T-SQL is not an all purpose programming language and you will get best results if the data retrieval is as simple as possible, i. e. without functions and such.
brgds
Philipp Post
brgds
Philipp Post
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply