A common request is how to select records from a table when a specific field starts with a number. This request often is used when searching free-form text fields where the fields were used to provide an answer field to a question.
The simplest way to attack this is by using a regular expression. For example:
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn regexp ‘^[0-9]+’;
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn RLIKE ‘^[0-9]+’;
Regex and RLIKE are compatible keywords that both represent regular expression matching.
The regex is described as follows :
^ – Start anchor, used to ensure the pattern matches start of the string.
[ – Start of character class.
0-9 – Any digit
] – End of character class
MySQL 5.0+ gave DBA’s the ability to write stored procedures and functions. This is a great addition, and one that all “real” DB engines should provide. But, as many DBA have found out, writing stored procedures using most MySQL clients can be a bit of a challenge. Usually after writing their first attempt, they are greeted with, “Error Code : 1064 You have have an error in your SQL syntax;”.
So, what is going on? Well, by default the MySQL statement delimiter is the semi-colon (;). However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.
The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:
CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )
SELECT Min(prod_price) INTO low_price FROM sku_items;
SELECT Max(prod_price) INTO high_price FROM items;
SELECT Avg(prod_price) INTO avg_price FROM items;
Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;. Any character may be used as the delimiter except for , as it is an escape character in mySQL.