Separating an address string in SQL

Question

I have a table called Houses on an sql server database that has a column containing (Danish) addresses. In Denmark the street name always comes before the house number and then the apartment information if it's an apartment. I want to separate the street name and the number into two strings and disregard the apartment information. My data looks like this:

Address
Fisker Vejen 48B, 1.TV
Baunevej 29

Thus, some street names have more than 1 word, and some adresses have apartment information and some don't. Some house numbers have non-numeric characters as well. I want it to be:

Street_Name      House_Number
Fisker Vejen     48B
Baunevej         29

I am able to extract the street name with the following code:

select case when a.NumStart> 0 then LEFT(a.Address,a.NumStart-1) ELSE a.Address END as Street_Name,
    FROM
    (select patindex('%[0-9]%',Address) as [NumStart], Address from Houses) a

but I can't get the house number without the floor information. Can anyone help?

Thanks!

Answer 1

Here is a solution:

SELECT *
        ,LEFT(Address,PATINDEX('% [0-9]%',Address)-1)'Street'
        , SUBSTRING(Address,PATINDEX('% [0-9]%',Address)+1,PATINDEX('%[0-9],%',Address+ ',')-PATINDEX('% [0-9]%',Address))'House Number'
FROM T

Demo: SQL Fiddle

UPDATE: If house number always starts with numbers and is followed by a comma or nothing at all, then this will work:

SELECT *
        ,LEFT(Address,PATINDEX('% [0-9]%',Address)-1)'Street'
        , SUBSTRING(Address,PATINDEX('% [0-9]%',Address)+1,PATINDEX('%, %',Address+ ', ')-PATINDEX('% [0-9]%',Address)-1)'House Number'
FROM Table1

Demo2: SQL Fiddle2

Answer 2

Try something like this:

SELECT
  CASE WHEN  a.NumStart> 0 then LEFT(a.Adresse, a.NumStart-1) ELSE a.Adresse END as Vejnavn,
  Substring(a.adress, a.Numstart, a.Comma - a.Numstart + 1) as HouseNumber
FROM (
  SELECT 
    PATINDEX('%[0-9]%', Adress) as [NumStart], 
    CHARINDEX(',', Adress + ',') as Comma,
    Adresse, 
    Salgsdato 
  FROM Houses) a