title: Separating an address string in SQL
body: <p>I have a table called <code>Houses</code> 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:</p>
  
  <pre><code>Address
  Fisker Vejen 48B, 1.TV
  Baunevej 29
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Street_Name      House_Number
  Fisker Vejen     48B
  Baunevej         29
  </code></pre>
  
  <p>I am able to extract the street name with the following code:</p>
  
  <pre><code>select case when a.NumStart&gt; 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
  </code></pre>
  
  <p>but I can't get the house number without the floor information. Can anyone help?</p>
  
  <p>Thanks!</p>