Solved

substring/charindex grappler!

Posted on 2009-05-07
3
841 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Replacing unrecognized function name in SQL Query 4 44
Server 2012 r2 and SQL 2014 6 33
What is needed to become a DBA? 7 50
SQL State HYT00. Timeout expired proplem 8 42
In this article I will describe the Backup & Restore 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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

737 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