Solved

substring/charindex grappler!

Posted on 2009-05-07
3
832 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:ditallop
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24332032
Why not use a Split UDF to divide the fields in 5?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24332195
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
 

Author Closing Comment

by:ditallop
ID: 31579277
Thanks so much!:-) I knew I needed to look at this solution differently!!!!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now