CODEWITHSUNDEEP
sqlsql-server
Pat Doyle
Pat Doyle

Reputation: 384

SQL - Extracting a substring between two characters

I am trying to pull out the name of a city from a long string in my database. Here is an example of what the data looks like for a few different locations. 

"701 MONROE STREET NW RUSSELLVILLE, AL 35653 (34.514971, -87.736372)"
"1825 HOLTVILLE ROAD WETUMPKA, AL 36092 (32.558544, -86.221265)"

I want to create a column for just the Name of the city. My thought was was to take everything Left of the fir comma and right of the following space. I have tried a few different ways to pull this but thing I might be missing something. 

SELECT  left(Location, CHARINDEX(',', location)) as city FROM table

This is returning everything left of the first comma.

"701 MONROE STREET NW RUSSELLVILLE,
"1825 HOLTVILLE ROAD WETUMPKA,

But now I want to return everything left of the comma and everything Right of the last space in this string and I am stumped as to how I would pull that information correctly. Any help would be appreciated.

Thanks, Pat

Upvotes: 2

Views: 285

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

If the Google API mentioned in my comment above is not an option. You can download (or even purchase) a ZIP Code database. The cost is nominal. I would suggest the quarterly updates because ZIP Codes change over time (add/edit/delete)

Example

Declare @YourTable table (id int,addr varchar(250))
Insert Into @YourTable values
(1,'701 MONROE STREET NW RUSSELLVILLE, AL 35653 (34.514971, -87.736372)'),
(2,'1825 HOLTVILLE ROAD WETUMPKA, AL 36092 (32.558544, -86.221265)')

Select A.ID
      ,StreetAddress =left(addr,nullif(charindex(Z.CityName,addr),0)-1)
      ,Z.CityName
      ,Z.StateCode
      ,Z.ZIPCode
 From @YourTable A
 Join [dbo].[OD-Zip] Z 
   on Z.ZipCode = substring(addr,nullif(patindex('%[0-9][0-9][0-9][0-9][0-9]%',addr),0),5)
      and charindex(Z.CityName,addr)>0
      and Z.ZipType='S'
      and Z.CityType='D'

Returns

ID  StreetAddress           CityName        StateCode   ZIPCode
1   701 MONROE STREET NW    Russellville    AL          35653
2   1825 HOLTVILLE ROAD     Wetumpka        AL          36092

Upvotes: 3

Koen
Koen

Reputation: 734

Using REVERSE could work with something along the lines of:

SELECT reverse(
            left(
             reverse(
               left(
                 Location, 
                 CHARINDEX(',', location)-1
               )
              ),
              CHARINDEX(' ', reverse(
                 left(
                   Location, 
                   CHARINDEX(',', location)-1
                 )
               )
             )
           )
  )as city FROM table;

Fiddle

Upvotes: 3

Related Questions