jquery - 将无结果更改为最接近的匹配select2

我想在select2中获得与邮政编码最接近的匹配。如果用户输入DE2，但是如果用户尝试输入其余的邮政编码，例如DE20BE，则不会显示“ BE”（当前设置为4个限制），但它可以与最接近DE20的匹配所以说DE2，现在返回下面的代码，没有结果，我想我需要不允许空格吗？：

这是我的MySQL查询：

SELECT `postcode` FROM `postcodes` WHERE `postcode` LIKE '%?%' ORDER BY `postcode` LIMIT 0,50;

这是JSON结果：

[{"ok":1,"text":"DE1"},{"ok":1,"text":"DE11"},{"ok":1,"text":"DE12"},{"ok":1,"text":"DE13"},{"ok":1,"text":"DE14"},{"ok":1,"text":"DE15"}]

这是JS：

var item = [];
function postCodeAjaxAutoComplete(element, url) {
        $(element).select2({
            placeholder: "Select a Postcode Area (E10)",
            minimumInputLength: 1,
            maximumInputLength: 4,
            multiple: false,
            id: function(e) { return e.text; },
            ajax: {
                url: url,
                dataType: 'json',
                data: function(term, page) {

                    return {
                        q: term
                    };
                },
                results: function (data, page) {
                    return { results: data };
                }
            },
            formatResult: formatResult,
            formatSelection: formatSelection,
            initSelection: function(element, callback) {
                var data = [];
                $(element.val().split(",")).each(function(i) {
                    data.push({
                        text: item[0]
                    });
                });
                callback(data);
            }
        });
    };
    postCodeAjaxAutoComplete('#ajax-postcode', '/ajax.php?do=postcode');

    function formatResult(data) {
        return data.text;
    };

    function formatSelection(data) {
        return data.text;
    };

最佳答案

请尝试这个sqlFiddle

SET @search = "DE20";

SELECT `postcode`,
   (SUBSTRING(CONCAT(@search,'^^^^'),1,1)=SUBSTRING(postcode,1,1)) +
   (SUBSTRING(CONCAT(@search,'^^^^'),2,1)=SUBSTRING(postcode,2,1)) +
   (SUBSTRING(CONCAT(@search,'^^^^'),3,1)=SUBSTRING(postcode,3,1)) +
   (SUBSTRING(CONCAT(@search,'^^^^'),4,1)=SUBSTRING(postcode,4,1)) as matches
FROM `postcodes`
HAVING matches > 0
ORDER BY matches DESC,LENGTH(postcode),postcode LIMIT 0,50;