Parsing addresses (and names) is perhaps one of the trickiest tasks in SQL. There's really no sure fire way to take a name or address that was originally placed in a single column and parse it 100% correctly considering the almost infinite variations. In my experience it's best in the long run to just split the entire string into individual columns then go about looking for exceptions. So with your address example, I'd split it (all the addresses in the table) into:
[USPS Address Delivery Line Components w/periods as spaces just used here for padding)
primaryAddressNumber preDirectional streetName suffix postDirectional secondaryAddressIndentifier secondaryAddress
================ =========== ======== ==== =========== ===================== =============
100..........................NW.................25..............St......[space]............APT#.............................303
Now if your address list is anything close to standard, this will cover the majority of the addresses, but you will then have to laboriously query the results to weed out and fix the data. The example above doesn't even address other things like PO Boxes, military APOs, Rural Route Numbers, etc.
So what I'd do FIRST is split all the address elements of each individual address into separate columns, then start grouping them by type of address (standard, military, PO Boxes, etc.) Your best resource after that is USPS.gov.
Now, to do the split, here's a function that I use for doing splits on spaces or nulls. It's basically the classic DelimitedSplit8K function with the delimiter hard-coded to be a space rather than any particular character.
[EDIT: My first post was of an obsolete version of the splitter. I've update the code below to the inline tvf version. Sorry for any inconvenience or for giving Jeff heartburn.]
WITH cteAddress(id,addr) AS
(
SELECT 1,'100 NW 25 St APT# 303' UNION ALL
SELECT 2,'10 SW 125 Ave' UNION ALL
SELECT 3,'234 Pine Ln Suite 303' UNION ALL
SELECT 4,'987-100 E Maple Ave' UNION ALL
SELECT 5,'345 3rd St Bungalow #9'
)
SELECT
a.id,
dsk.ItemNumber,
dsk.Item,
a.addr
FROM
cteAddress a
CROSS APPLY
dbo.itvfDelimitedSplit8K_NULLS(a.addr) dsk
CREATE FUNCTION [dbo].[itvfDelimitedSplit8K_NULLS]
(
@pString VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH
E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS
(
SELECT 1 FROM E1 a, E1 b
),
E4(N) AS
(
SELECT 1 FROM E2 a, E2 b
),
cteTally(N) AS
(
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS
(
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = ' ' OR t.N = 0)
)
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(' ',@pString,s.N1),0)-s.N1,8000))
FROM
cteStart s
)
GO