Solved

Seperate Copmbined database fields

Posted on 2009-04-06
6
241 Views
Last Modified: 2012-06-21
I have an imported spreadsheet that I need to break apart some information in the database table.  The follwoing is an example of the information.  What I need to do is break apart strGrade so it sperates the grade and rank in different fields, then I need to break aprt the city state and zip into different fields as well.  This is currently how all the data looks in fields.
strGrade                                       strCity  
(E5) SSG                                PEMBINA ND 58271    
(05) LTC                           LAKEVILLE MN 55044-8203      

I need it to look like below
strGrade              strRank                 strCity              strState              strZip

E5                            SSG                Pembina                 ND                   58271

O5                           LTC                 LakeVille                 MN                    55044

With the last 4 of zip dropped off, some have them some do not.

Open in new window

0
Comment
Question by:kdeutsch
  • 3
  • 3
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24079914
Something like this should work, preserving the ZIP+4:

SELECT strGrade, strRank, LEFT(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity) - 1) AS strCity,
      LTRIM(SUBSTRING(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity), 3)) AS strState,
      RTRIM(SUBSTRING(strCity, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', strCity), 10)) AS strZip
FROM SomeTable

To kill the ZIP+4...

SELECT strGrade, strRank, LEFT(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity) - 1) AS strCity,
      LTRIM(SUBSTRING(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity), 3)) AS strState,
      SUBSTRING(strCity, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', strCity), 5) AS strZip
FROM SomeTable


Both queries handle city names with >1 word quite handily.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24080069
kdeutsch,

Sorry, I did not notice splitting the first column :)

Something like this should work, preserving the ZIP+4:

SELECT SUBSTRING(strGrade, 2, CHARINDEX(')', strGrade) - 2) AS strGrade,
      RTRIM(SUBSTRING(strGrade, CHARINDEX(' ', strGrade) + 1, 100)) AS strRank,
      LEFT(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity) - 1) AS strCity,
      LTRIM(SUBSTRING(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity), 3)) AS strState,
      RTRIM(SUBSTRING(strCity, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', strCity), 10)) AS strZip
FROM SomeTable

To kill the ZIP+4...

SELECT SUBSTRING(strGrade, 2, CHARINDEX(')', strGrade) - 2) AS strGrade,
      RTRIM(SUBSTRING(strGrade, CHARINDEX(' ', strGrade) + 1, 100)) AS strRank,
      LEFT(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity) - 1) AS strCity,
      LTRIM(SUBSTRING(strCity, PATINDEX('% [A-Z][A-Z] [0-9]%', strCity), 3)) AS strState,
      SUBSTRING(strCity, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', strCity), 5) AS strZip
FROM SomeTable

Regards,

Patrick
0
 

Author Comment

by:kdeutsch
ID: 24081822
I get 82 records into it and then I get this error that pops up  is there  a way to tell which record it is stopping on.

Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

(83 row(s) affected)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:kdeutsch
ID: 24081855
Here are some results from the 82 files it treid to process

strGrade   strRank    strCity                                            strState strZip
---------- ---------- -------------------------------------------------- -------- ----------
E5         SSG        PEMBINA                                            ND       58271
E5         SSG        LAKEVILLE                                          MN       55044-8203
E6         TSG        DULUTH                                             MN       55815
05         LTC        BLOOMINGTON                                        MN       55437
E4         SRA        CRYSTAL                                            MN       55427
E6         TSG        ST PAUL                                            MN       55106
E6         TSG        SAINT PAUL                                         MN       55119
E7         MSG        SAVAGE                                             MN       55378-2826
E3         A1C        LINO LAKES                                         MN       55014-1944
E7         MSG        DULUTH                                             MN       55804-2042
05         LTC        MINNEAPOLIS                                        MN       55417-1523
E5         SSG        COTTAGE GROVE                                      MN       55016
E5         SSG        EXCELSIOR                                          MN       55331
E5         SSG        DULUTH                                             MN       55808-0000
E4         SRA        EDEN PRAIRIE                                       MN       55344-4089
E6         TSG        DULUTH                                             MN       55811
05         LTC        WOODSTOCK                                          IL       60098-7182
E4         SRA        BROOKLYN PARK                                      MN       55443
E9         CMS        BARNUM                                             MN       55707-9614
E5         SSG        SAINT PAUL                                         MN       55116
E3         A1C        DULUTH                                             MN       55810
E7         MSG        DULUTH                                             MN       55811-5704
E7         MSG        DULUTH                                             MN       55804-1228
E6         TSG        ESKO                                               MN       55733
E6         TSG        BENSON                                             MN       56215
E6         TSG        WOODBURY                                           MN       55125
E7         MSG        HERMANTOWN                                         MN       55811-3701
E9         CMS        DULUTH                                             MN       55808-1338
E7         MSG        APPLE VALLEY                                       MN       55124-9373
E4         SRA        BELLE PLAINE                                       MN       56011
05         LTC        HUGO                                               MN       55038-9297
E4         SRA        SUPERIOR                                           WI       54880-6908
01         2LT        HUGO                                               MN       55038
E4         SRA        WICHITA                                            KS       67207-1111
E6         TSG        DULUTH                                             MN       55807
04         MAJ        OAKDALE                                            MN       55128-5805
E4         SRA        DULUTH                                             MN       55804-1267
E4         SRA        DULUTH                                             MN       55816
02         1LT        COTTAGE GROVE                                      MN       55016
E4         SRA        PARKERS PRAIRIE                                    MN       56361-4778
E5         SSG        PARKERS PRAIRIE                                    MN       56361-0000
E5         SSG        SUPERIOR                                           WI       54880
E7         MSG        MINNETONKA                                         MN       55345-6067
E6         TSG        COON RAPIDS                                        MN       55448-4425
03         CPT        FARMINGTON                                         MN       55024
E4         SRA        GREEN ISLE                                         MN       55338
E5         SSG        SAINT PAUL                                         MN       55104
E6         TSG        GREENDALE                                          WI       53129
E4         SRA        INVER GROVE HEIGHTS                                MN       55076
E5         SSG        DULUTH                                             MN       55805
E3         A1C        MAPLE GROVE                                        MN       55311
E5         SSG        DULUTH                                             MN       55803-9245
E6         TSG        DULUTH                                             MN       55807-1833
06         COL        APPLE VALLEY                                       MN       55124-7306
E4         SRA        ASHTABULA                                          OH       44004-5114
E4         SRA        HERMANTOWN                                         MN       55811-3917
06         COL        DULUTH                                             MN       55811-9709
04         MAJ        MINNEAPOLIS                                        MN       55417
E4         SRA        VERNON CENTER                                      MN       56090
E5         SSG        SOUTH SAINT PAUL                                   MN       55075
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24086222
kdeutsch said:
>>I get 82 records into it and then I get this error that pops up  is there  a way to tell which record it is stopping on.

That indicates that you have at least one record that is not following the pattern that you described in your
question.

Based on your samples, I expected to see the following for strGrade:

      

and for strCity:

      <2-letter state code>

Look for records that violate those patterns.  For example, having two spaces between the state and zip will
cause that error.  Try something like this:

SELECT *
FROM SomeTable
WHERE PATINDEX('% [A-Z][A-Z] [0-9]%', strCity) = 0

That will find records that have extra spaces between the state and zip, or perhaps a comma or some other
punctuation following the state, or perhaps an omitted state or zip.

In any event, I believe I answered your question :)
0
 

Author Closing Comment

by:kdeutsch
ID: 31567145
Thanks for the help
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

11 Experts available now in Live!

Get 1:1 Help Now