Address String to Address Fields VIEW or SELECT

Question

I have an address field that is a single line that looks like this:

Dr Robert Ruberry, West End Medical Practice, 38 Russell Street, South Brisbane 4101

I am wanting to write a view that will split that address into Name, Addr1, Addr2, Suburb, Postcode fields for reporting purposes.

I have been trying to USE SUBSTRING and CHARINDEX like this but it doesnt seem to split it correctly.

SUBSTRING([address_Field],CHARINDEX(',',[address_Field]),CHARINDEX(',',[address_Field]))

Can anyone help? TIA

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

Here's a couple of options for you. If you're just looking for a quick answer, see this similar question that's already been answered:

T-SQL split string based on delimiter

If you want some more in depth knowledge of the various options, check this out:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

edited May 23 '17 at 11:46

Community

1
1

answered May 28 '15 at 00:56

John Hodge

1,645
1
13
13

score 0 · Accepted Answer · answered May 28 '15 at 01:48

may be this works for your requirement

IF OBJECT_ID('tempdb..#test') IS NOT NULL
    DROP TABLE #test
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'Dr Robert Ruberry, West End Medical Practice, 38 Russell Street, South Brisbane 4101')


DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT 
    @select='
        select p.col1 As Name,p.col2 as Addr1,p.col3 as Addr3,p.col4 as Postcode
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

score 0 · Answer 3 · answered Jan 11 '16 at 22:16

This answer does not specifically apply to SQL, but it does apply to street addresses.

If you are willing to take a dependency on a third-party, you could send street addresses to the SmartyStreets International Street API service.

To do this, you submit an HTTP GET request.

Your example address would look like this:

curl -v 'https://international-api.smartystreets.com/verify?
    auth-id=YOUR+AUTH-ID+HERE&auth-token=YOUR+AUTH-TOKEN+HERE&
    address1=Dr%20Robert%20Ruberry%2C%20West%20End%20
        Medical%20Practice%2C%2038%20Russell%20Street%2C%20
        South%20Brisbane%204101
    &country=aus'

(Notice that the address is url encoded. The request is wrapped for readability.)

The response would be a JSON string, separated into components that you can then insert into your database however you need to:

[
    {
        "organization": "Dr Robert Ruberry, West End Med.",
        "address1": "Dr Robert Ruberry, West End Med.",
        "address2": "Russell Street",
        "address3": "38 Practice",
        "address4": "South Brisbane QLD 4101",
        "components": {
            "administrative_area": "QLD",
            "building": "Russell Street",
            "country_iso_3": "AUS",
            "locality": "South Brisbane",
            "postal_code": "4101",
            "postal_code_short": "4101",
            "thoroughfare": "Practice",
            "thoroughfare_name": "Practice",
            "sub_building_number": "38"
        },
        "metadata": {},
        "analysis": {
            "verification_status": "Partial",
            "address_precision": "Locality",
            "max_address_precision": "DeliveryPoint"
        }
    }
]

An added benefit is that the service provides you with extra information about the validity of the address.

(Disclosure: I work at SmartyStreets.)

Address String to Address Fields VIEW or SELECT

3 Answers3