Check for data prefix in the mysql table

I need to write a Mysql function which will check for the feature entry in the SQL table named FeatureEntry. Data in the FeatureEntry will be like:

    mysql> select * from FeatureEntry ;
+----+------+---------+
| Id | Code | Feature |
+----+------+---------+
|  1 | 121  | test1   |
|  2 | 122  | test2   |
|  3 | 123  | te开发者_如何学Pythonst3   |
|  5 | 125  | test5   |
|  6 | 126  | test6   |
|  7 | 127  | test7   |
|  8 | 128  | test8   |
+----+------+---------+
7 rows in set (0.00 sec)

Function will pass number1 as argument and return 0 if the number is prefixed with any of the Code entry made in the FeatureEntry table. Else function will return 0; For example when I call the mysql function check_for_feature_code(1215676767), it should return 0 as it contains the prefix 121 which is present in the table.

mysql> select check_for_feature_code(1215676767);
+-------------------------------+
| check_for_feature_code(12156767671) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql>

If I call the function check_for_feature_code(67675676767), it should return 1 as it doesn't match the prefix with codes in the table.

PS -- Codes are strings not integers.

Here is a function (tested with your posted dataset in MySQL 5.1) that returns 0 if a given number is prefixed with a Code from the FeatureEntry table, otherwise it returns 1.

CREATE FUNCTION `check_for_feature_code`(`num_in` INT)
 RETURNS TINYINT
 LANGUAGE SQL
 NOT DETERMINISTIC
 READS SQL DATA
 SQL SECURITY DEFINER
 COMMENT 'Checks for a feture code prefix'
BEGIN
 DECLARE is_featured INT;
 SELECT COUNT(*) INTO is_featured FROM FeatureEntry WHERE Code = SUBSTR(CONVERT(num_in, CHAR), 1, LENGTH(Code));
 IF is_featured = 0 THEN RETURN 1;
 END IF;
 RETURN 0;
END;

This is now working for a variable length code prefix.