Splitting Address Field

[yanios]

I'm new at this and just can't seem to get it. Any help is appreciated.
I have a table with an address field that is all together
eg: 1600 Market street
123 s Market Rd
12345 Market Blvd
1 W Market Way
I need to break the field into AddrNo, AddrDir, AddrName, AddrSteet.
I've tried patindex and charindex but still can't get it.
Any suggestions?
Thank you.

 

[gradley]

See if this helps to get you started.

Declare @strAddress varchar(500)
Declare @strAddNo varchar(10)
Declare @strAddStreet varchar(50)
Declare @intPointer integer

SET	@strAddress = '12345 Market Blvd'


SET	@intPointer = CHARINDEX(' ', @strAddress,1)
SET	@strAddNo = Left(@strAddress,@intPointer - 1)
SET	@strAddStreet = Substring(@strAddress, @intPointer + 1, Len(@strAddress) - @intPointer + 1)

You can use the CHARINDEX and pointer logic for however many fields you need to separate.

 

[donutman]

Passing a list of values to a Stored Procedure (Part II) faq183-5207. That FAQ won't do the entire job, but you can use it as a tool to help you parse the field. There's no direct solution to this problem which has come up several times in this forum. The best approach is to progressive strip off one field at a time and view the results. Fix any errors manually and move on to the remaining portion of the field.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yanios]

donutman,
Thank you for posting. I've been trying it. It does work. But, how can I get the
SET @strAddress to read my address field in the table instead of making it = '12345 Market Blvd'? The more I seem to 'learn' the more I seem to get confused.

 

[donutman]

Gradley was showing that as an example of how his code would work. He's suggesting that you replace it with the column name for your table. You're going to need to run a lot of small SQL jobs. Are you familiar with T-SQL?
If not consult this page

http://www.tek-tips.com/faq.cfm?pid=183

and look for the section "Resources".
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]