[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Seperate Copmbined database fields

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
kdeutsch
Asked:
kdeutsch
  • 3
  • 3
1 Solution
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
kdeutschAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kdeutschAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
kdeutschAuthor Commented:
Thanks for the help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now