[RESOLVED] MySQL query: Select only numeric substring from Varchar field?

[Joseph_Sliker]

Hi;

I would like to write a MySQL query that will limit the selected records to a specific pattern that end in digits rather than non-numeric characters.

Say the field (TESTPHASE) has values like this
A000
APM
APM1
A015
A010
A045
HPM1
GPM
F020
E045

I want the SELECT query to return only

A000
A015
A010
A045
F020
E045

I suppose it would be possible to filter these after the fact with some fancy php code, but I'd MUCH rather do it up front with the MySQL query.

Oh, and of course I am stuck with MySQL v3.23.45

Any suggestions? Thanks

[dougal85]

I think the answer will be here somewhere : http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

I cant remember off hand for that version of mySQL

[tomhath]

Regular expression. But I don't recall if 3.23 had them.

http://dev.mysql.com/doc/refman/4.1/en/regexp.html

[Joseph_Sliker]

I had looked over the string functions section and did not find anything that applied, but the REGULAR EXPRESSIONS stuff was all new to me....and I was able to make it work!

Here is a sample code that seems to do what I want (I apply the REGEXPR in the WHERE section:

SELECT TESTPHASECODES.COLLAPSE_1, SUBSTRING(DATAORF.TESTPHASE, 2) AS STEM, PHASENAME, COUNT(DATAPOINTID) AS SCORECOUNT FROM DATAORF LEFT JOIN TESTPHASECODES ON DATAORF.TESTPHASE = TESTPHASECODES.TESTPHASE  

WHERE SCHOOLID LIKE '195' AND LANG LIKE 'ENG' AND GRADE LIKE '3' AND ROOM LIKE '%' AND DATAORF.TESTPHASE LIKE 'H%' 

AND SUBSTRING(DATAORF.TESTPHASE, 2, 2) REGEXP '[[:digit:]]+'=1 

AND PROBLEV=GRADE AND ORF_MWRC IS NOT NULL 
GROUP BY TESTPHASECODES.PHASENAME ORDER BY STEM

I should probably be using HAVING instead of WHERE since this is a GROUP BY query, but this does seem to work.

Thanks for the help!