StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql extract alphabetical characters only
text
Body
copied!<p>I have a column which contains a mixture of letters and numbers. I need to get the first set of alphabetical characters from the column. As soon as an integer is encountered I don't want any more.. for example:</p> <p>Take 5 rows: </p> <ul> <li>S123J90 </li> <li>SF91K88 </li> <li>JK12K99</li> <li>X129PL9</li> <li>ABCD123</li> </ul> <p>I need the following data from these 5 rows</p> <ul> <li>S </li> <li>SF </li> <li>JK </li> <li>X </li> <li>ABCD</li> </ul> <p>I have found a lot of talk about nested REPLACE queries but I'm sure there is a better option out there?</p> <p>Note: All rows start with at least one alphabetical character</p> <hr> <p>Update: I used a modification of this function <a href="http://www.rummandba.com/2011/02/mysql-function-to-remove-non.html" rel="nofollow">http://www.rummandba.com/2011/02/mysql-function-to-remove-non.html</a> as follows</p> <pre><code>DROP FUNCTION IF EXISTS remove_non_alpha_char_f // CREATE FUNCTION remove_non_alpha_char_f (prm_strInput varchar(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE i INT DEFAULT 1; DECLARE v_char VARCHAR(1); DECLARE v_parseStr VARCHAR(255) DEFAULT ' '; WHILE (i <= LENGTH(prm_strInput) ) DO SET v_char = SUBSTR(prm_strInput,i,1); IF v_char REGEXP '^[A-Za-z ]+$' THEN #alpha SET v_parseStr = CONCAT(v_parseStr,v_char); SET i = i + 1; ELSE SET i=256; END IF; END WHILE; RETURN trim(v_parseStr); END </code></pre> <p>Note: I have re-coded the function for this answer so the syntax might be out.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload