title: Displaying whole table after stripping characters in SQL Server
body: <p>This question has 2 parts.</p>
  
  <h2>Part 1</h2>
  
  <p>I have a table "Groups":</p>
  
  <pre><code>group_ID     person
  -----------------------
  1            Person 10
  2            Person 11
  3            Jack
  4            Person 12
  </code></pre>
  
  <p>Note that not all data in the "person" column have the same format.</p>
  
  <p>In SQL Server, I have used the following query to strip the "Person " characters out of the person column:</p>
  
  <pre><code>SELECT
  REPLACE([person],'Person ','')
  AS [person]
  FROM Groups
  </code></pre>
  
  <p>I did <strong>not</strong> use UPDATE in the query above as I do not want to alter the data in the table.</p>
  
  <p>The query returned this result:</p>
  
  <pre><code>person
  ------
  10
  11
  12
  </code></pre>
  
  <p>However, I would like this result instead:</p>
  
  <pre><code>group_ID     person
  -------------------
  1            10
  2            11
  3            Jack
  4            12
  </code></pre>
  
  <p>What should be my query to achieve this result?</p>
  
  <h2>Part 2</h2>
  
  <p>I have another table "Details":</p>
  
  <pre><code>detail_ID     group1     group2
  -------------------------------
  100           1          2
  101           3          4
  </code></pre>
  
  <p>From the intended result in Part 1, where the numbers in the "person" column correspond to those in "group1" and "group2" of table "Details", how do I selectively convert the numbers in "person" to integers and join them with "Details"?</p>
  
  <p>Note that all data under "person" in Part 1 are strings (nvarchar(100)).</p>
  
  <p>Here is the intended query output:</p>
  
  <pre><code>detail_ID     group1     group2
  -------------------------------
  100           10         11
  101           Jack       12
  </code></pre>
  
  <p>Note that I do not wish to permanently alter anything in both tables and the intended output above is just a result of a SELECT query.</p>