Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

substring/charindex grappler!

Posted on 2009-05-07
3
Medium Priority
?
846 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:Paula DiTallo
  • 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:Paula DiTallo
ID: 31579277
Thanks so much!:-) I knew I needed to look at this solution differently!!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

926 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