A Little Noise

October 12, 2011

Extract range characters

Filed under: MySQL — snoyes @ 11:15 am

Given a source string and a two-character range string, extracts all characters from the source that fall within the range. Sort of a simple version of the [A-Z] syntax from regular expressions.

Examples:
extractRange(‘123-ABC-456’, ‘AZ’) returns ‘ABC’
extractRange(‘123-ABC-456′, ’09’) returns ‘123456’

CREATE FUNCTION extractRange(inString TEXT, inRange char(2)) 
RETURNS TEXT 
DETERMINISTIC
BEGIN
DECLARE returnString TEXT DEFAULT '';
DECLARE pointer INT DEFAULT 1;
WHILE pointer <= CHAR_LENGTH(inString) DO
    IF MID(inString, pointer, 1) BETWEEN LEFT(inRange, 1) AND RIGHT(inRange, 1) THEN
        SET returnString := CONCAT(returnString, MID(inString, pointer, 1));
    END IF;
    SET pointer := pointer + 1;
END WHILE;
return returnString;
END

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress