title: SQL Filter numbers between x and x and ignore strings
body: <p>I have a table in SQL Server where I need to filter rooms by name and type. The problem is, that the names are stored as <code>varchar</code> and there are also some rooms with letters. I need to filter out the rooms with letters before I can compare them as <code>int</code> or otherwhise I will get an error. </p>
  
  <p>Here's an example from Room.Name:</p>
  
  <pre><code>030
  210a
  210b
  Lan-Room-A
  240
  </code></pre>
  
  <p>I can work around the room names with <code>a</code> or <code>b</code> with <code>LEFT(Rooms.Name, 3)</code> but if I want to add <code>(LEFT(Rooms.Name, 3) BETWEEN 0 and 350</code> and it gets to <code>Lan-Room-A</code> it oviously can't convert a string to int. I also need to do additional filtering like <code>Room.Type = 6</code> for example.</p>
  
  <pre><code>SELECT
      Room.Name,
      Room.Descr,
      Room.MainUser
  WHERE
      LEFT(Room.Name, 1) NOT LIKE '%[0-9]%' 
      AND LEFT(Room.Name, 3) BETWEEN 0 AND 350  
      AND Room.Type = 6
  </code></pre>
  
  <p>(Removed some joins for simplicity)</p>
  
  <p>I simply need to filter out the rows which contain strings before the when clause, but I have no idea how.</p>
  
  <p>Do you guys have any idea?</p>
  
  <p>Please note that I can't edit the database.</p>
  
  <p>Thanks in advance.</p>