CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

SQL Server - Split string on last occuring number

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?

Upvotes: 0

Views: 69

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

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))

Upvotes: 1

Alex K.
Alex K.

Reputation: 175826

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

Upvotes: 1

Related Questions