title: SQL Regex get phone number
body: <p>I want to get the phone number of a text string that meets certain conditions:</p>
  
  <ul>
  <li>9 characters (numbers)</li>
  <li>You can only start at 9,8,7,6</li>
  </ul>
  
  <p>I tried with the expression: </p>
  
  <pre><code>'^\(?(9|8|7|6)\)?([0-9]{8}))$' 
  </code></pre>
  
  <p>in the following function:</p>
  
  <pre><code>DECLARE @str VARCHAR(MAX)
  DECLARE @validchars VARCHAR(MAX)
  
  SET @str = 'TESTING1234 997711056'
  SET @validchars = '^\(?(9|8|7|6)\)?([0-9]{8}))$' 
  
  WHILE PATINDEX('%' + @validchars + '%',@str) &gt; 0
  SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 1) ,'')
  
  
  SELECT @str
  </code></pre>
  
  <p>I need to get 997711056 but I always get the complete text string. What is my mistake?</p>