?
Solved

substring/charindex grappler!

Posted on 2009-05-07
3
Medium Priority
?
844 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

801 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