title: Strip numbers from MySQL SELECT
body: <p>I need to remove numbers from a name field in a SELECT query. The numbers are likely to be at the end of the string. Examples would be as follows:</p>
  
  <pre><code>Some Name
  Namewithnospace
  NamewithInt 100   &lt;--- I want to remove this int from the string
  </code></pre>
  
  <p>I cannot guarantee that the first part of the string won't contain a space, although I can be fairly sure that the number will be the last part of the string. Also most of the data won't have a space.</p>
  
  <p>I can see that there must be a solution using <code>REVERSE()</code> and <code>SUBSTRING()</code> but can't work out how not to mangle the data that either has no spaces, or those that have a space but no integer.</p>
  
  <p>edit: below is the definition for the MySQL C function that Ollie pointed me towards, with a change made by me to the regexp (to include spaces and punctuation in the function's output):</p>
  
  <pre><code>DELIMITER !
  
  DROP FUNCTION IF EXISTS alphas! 
  
  CREATE FUNCTION alphas ( str VARCHAR(255) ) 
                 RETURNS VARCHAR(255) 
                 DETERMINISTIC
  BEGIN 
    DECLARE i, len SMALLINT DEFAULT 1; 
    DECLARE ret VARCHAR(255) DEFAULT ''; 
    DECLARE c VARCHAR(1); 
    SET len = CHAR_LENGTH( str ); 
    REPEAT 
      BEGIN 
        SET c = MID( str, i, 1 ); 
        IF c REGEXP '[[:alpha:]]' OR c REGEXP '[[:space:]]' THEN 
          SET ret=CONCAT(ret,c); 
        END IF; 
        SET i = i + 1; 
      END; 
    UNTIL i &gt; len END REPEAT; 
    RETURN ret; 
  END !
  DELIMITER ; 
  </code></pre>