0

I have following column (with fictional data):

Location
---------------
15630London
45680Edinburg
138739South Wales

This column contains both Zipcodes and City names. I want to split those 2 into 2 seperate columns.

So in this case, my output would be: 

Zip    | City
-------|---------
15630  | London
45680  | Edinburg
138739 | South Wales

I tried the zipcode with 

LEFT(location,LEN(location)-CHARINDEX('L',location))

But I couldn't find out how to set the CHARINDEX to work on all letters.

Any suggestions / other ideas?

DenStudent
  • 906
  • 1
  • 13
  • 37
  • Is zip always 5 digits? – Alex K. Jan 16 '17 at 15:40
  • All the entries you provided start with five digits. Can we rely on this, or is coincidental? – Mureinik Jan 16 '17 at 15:40
  • @AlexK. That's coincedence. Can be more or less then 5 digits – DenStudent Jan 16 '17 at 15:41
  • You might see [split alpha and numeric using sql](http://stackoverflow.com/a/27010603/2026740) – Daniel Corzo Jan 16 '17 at 15:44
  • Why does `Location` contain two different data items in the first place? Shouldn't you fix whatever code creates such data? Furthermore, given that UK Zip codes are 6 letters and numbers (eg my home's zip in the 90s was M60 7LH) you may *not* be able to split or parse actual data – Panagiotis Kanavos Jan 16 '17 at 15:45
  • @DenStudent UK zip code don't use this format. You'll get into trouble if you assume that all zip codes are numbers – Panagiotis Kanavos Jan 16 '17 at 15:48
  • @PanagiotisKanavos The data is completely random. I just didn't want to use data from my database here – DenStudent Jan 16 '17 at 15:52
  • 1
    @DenStudent just don't mix up the data then. This breaks 1st Normal Form, the most fundamental rule - don't mix up two different attributes in the same column. It's far easier to store the data correctly than fixing it after the fact – Panagiotis Kanavos Jan 16 '17 at 15:53
  • @PanagiotisKanavos I agree with that. But this is how I received the data. I would personally never store them this way – DenStudent Jan 16 '17 at 15:55
  • @DenStudent it's easier to clean up the data *before* entering it in the database. T-SQL isn't very good at string formatting and manipulation. A regex could extract each field and expose it by name, eg `(?\d+)(?.*)` would allow you to extract the zip code and text by name with `match.Groups["zip"].Value` and `match.Groups["city"].Value` – Panagiotis Kanavos Jan 16 '17 at 15:57

3 Answers3

2

Here is one way using PATINDEX and some string functions

SELECT LEFT(Location, Patindex('%[a-z]%', Location) - 1),
       Substring(Location, Patindex('%[a-z]%', Location), Len(Location))
FROM   (VALUES ('15630London'),
               ('45680Edinburg'),
               ('138739South Wales'))tc(Location)

Note : Above code considers always zip codes are numbers and characters start only with city name and city is present in all the rows (ie) strings are present in every row

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

Detect the first non-numeric character and pull of that many chars from the left, then read beyond that point to the end:

select 
    left(Location, patindex('%[^0-9]%', Location) - 1),
    substring(Location, patindex('%[^0-9]%', Location), len(Location))
from t
Alex K.
  • 171,639
  • 30
  • 264
  • 288
1
declare @string varchar(200)='15630London'

select substring(@string,1,patindex('%[a-z,A-Z]%',@string)-1),
substring(@string,patindex('%[a-z,A-Z]%',@string),len(@string))
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94