I have a customer who is running Microsoft C5 as their ERP System, and we are supposed to help them get it integrated to their CRM System. For that i have created an SQL view where i map the fields needed for importing customers according to our import program's standard. The problem is that this version of microsoft C5 stores Zip code and city in the same field, so i need to split it up into a zip field and a city field. The problem is that there isn't really a common pattern of how many whitespaces between zipcode and city, and the zipcodes aren't equally long and so forth. i have tried many string functions, but i bet it's piece of cake to solve if you just know how.
Here are some examples of values in the zipcity collumn i need to split:
D-40883 Ratingen
N-4300 Sandnes
S-55117 Jönköping
3650 Ølstykke
PL - 58-100 swidnica dlaska
As you see, here we got a mix of german, danish, norwegian, swedish and polish zipcodes an citynames. This is what i need to have splitted.
I Don't know whether i should try with a regular expression? I am not very skilled in using regular expressions, so help would be much appreciated.