?
Solved

Seperate Copmbined database fields

Posted on 2009-04-06
6
Medium Priority
?
288 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
[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
  • 3
  • 3
6 Comments
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

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 93

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

Technology Partners: 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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 extract information from SQL Server on Database, Connection and Server properties

762 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