substring/charindex grappler!

I've been working on this statement for 2 hours. I need another pair of techie-eyes to review this before I wander even farther into the jungle growth I've created for myself!;-) If there's a better way to achieve the same goal without using (overusing) the charindex/substring function combination please let me know!;-)

Here's the goal-- I have thousands of denormalized addresses (along with other contact info) arriving in a text file that look like:
 
28944 Timberlane,,Agoura Hills,CA,91301
32 Union Square East, Ste 608,New York, NY, 10003

which I am trying to migrate into the columns: address1, address2,city,state,postalCode in a normalized table.

Here is an example of what I've been able to retrieve so far with the current t-sql statement I created (see code snippet):

32 Union Square East (address1) - correct
Ste 608 (address2) - correct
New York, NY (City) - wrong
NULL (state) -  Here's where I got really lost and decided to post this!;-)
10003 (postalCode) - correct

select 
  last,
  first,
  address,
  LEFT(address, CHARINDEX(',', address) - 1) as 'Address1',
  SUBSTRING(address, CHARINDEX(',',address) + 1, CHARINDEX(',',address, CHARINDEX(',',address) + 1) - (CHARINDEX(',', address) + 1)) as 'Address2',
  SUBSTRING(address, CHARINDEX(',',address, CHARINDEX(',',address) + 1) + 1, DATALENGTH(address) - CHARINDEX(',', address, CHARINDEX(',', address) + 1) - CHARINDEX(',', REVERSE(address))) as 'City',
  ltrim(RIGHT(address, CHARINDEX(',', REVERSE(address)) - 1)) as 'PostalCode', 
  phone,
  mobile,
  fax,
  States
from sw_salesforce
where website is not null 
 and  address is not null

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
 
Anthony PerkinsCommented:
You could write a function like this:

Create Function [dbo].[udf_SplitAddress](
            @AddressLine varchar(200)
      )

Returns @Address table (
      Address1 varchar(50),
      Address2 varchar(50),
      City varchar(50),
      State char(2),
      ZIP varchar(10)
      )

As

Begin

Declare      @Pos smallint,
            @OldPos smallint,
            @Value varchar(50),
            @Item tinyint,
            @Address1 varchar(50),
            @Address2 varchar(50),
            @City varchar(50),
            @State char(2),
            @ZIP varchar(10)

Select      @Pos = 1,
            @OldPos = 1,
            @Item = 0

While @Pos > 0
      Begin
            Set @Pos = CHARINDEX(',', @AddressLine, @OldPos)

            If @Pos > 0
                  Select      @Value = LEFT(LTRIM(SUBSTRING(@AddressLine, @OldPos, @Pos - @OldPos)), 50),
                              @OldPos = @Pos + 1
            Else
                  Set @Value = LEFT(LTRIM(SUBSTRING(@AddressLine, @OldPos, LEN(@AddressLine) - @OldPos + 1)), 50)
            
            Set @Item = @Item + 1

            If @Item = 1
                  Set @Address1 = @Value
            ELSE If @Item = 2
                  Set @Address2 = @Value
            ELSE If @Item = 3
                  Set @City = @Value
            ELSE If @Item = 4
                  Set @State = LEFT(@Value, 2)
            ELSE If @Item = 5
                  Set @ZIP = LEFT(@Value, 10)
      End

Insert @Address(Address1, Address2, City, State, ZIP)
Values (@Address1, @Address2, @City, @State, @ZIP)

Return

End

And then execute it like this:
Select *
From      dbo.udf_SplitAddress('28944 Timberlane,,Agoura Hills,CA,91301')
0
 
Anthony PerkinsCommented:
Why not use a Split UDF to divide the fields in 5?
0
 
Paula DiTalloIntegration developerAuthor Commented:
Thanks so much!:-) I knew I needed to look at this solution differently!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.