Mysql函数删除少于3个字符的单词 [英] Mysql function remove words with less than 3 characters

问题描述

我有问题.

在我的数据库中，我有某些存储过程对我拥有的关键词进行搜索.在我的GUI(在asp.net中)中，有一个函数可以解决我的问题，但是在MySQL中，我需要相同的函数.

In my database i have certain stored procedures that perform a search on keywords i have. In my GUI (in asp.net) i have a function that takes care of my problem, but i need the same function in Mysql.

我需要从少于3个字符的任何单词中删除一个字符串，而只保留包含更多单词的单词.例如，阿姆斯特丹的酒店"将返回阿姆斯特丹的酒店".

I need to strip a string from any words less than 3 characters and only keep the words that have more. So for instance, "hotels in amsterdam", would return "hotels amsterdam".

该字段位于innodb全文本上，因此，由于存储过程将所有"替换为"+"，因此我实际上需要删除所有少于3个单词.FULLTEXT搜索处于布尔模式.我根本不需要在任何行上执行此操作.我只需要一个从少于3个字符的单词中剥离字符串的函数.该函数将在存储过程中使用，然后该存储过程将在修改后的字符串值上搜索行

The fields are on innodb fulltext, for this reason i need to actually remove all words less than 3 becuase my stored procedure takes all " " and replaces it with " +". The FULLTEXT search is in boolean mode. I dont need to perform this on any row at all. I just need A function that strips a string from words that are less than 3 characters. The function will be used in a stored procedure and that stored procedure will then search on rows on the modified string value

我的sql编程技能不如.Net出色，并且我一直在寻找2天的功能，该功能可以帮助我实现这一目标，而没有运气.

My sql programming skills are not as good as in .Net and ive been searching for 2 days for a function that can do this for me without luck.

很抱歉，但是到目前为止，我什至没有任何代码可以启动，其他人对如何继续或解决此问题有想法吗?由于存储函数中不允许使用动态sql或触发器，因此im完全卡住了！

Im sorry but i dont even have any code so far to get things started, does anyone else have an idea on how to proceed or attack this problem? Since dynamic sql is not allowed in stored functions or triggers im completely stuck!

提前谢谢！

推荐答案

假设只有空格将单词分开，则可以使用以下内容:-

Assuming that only spaces split the words, you can use something like the following:-

SELECT SomeId, GROUP_CONCAT(SomeWord ORDER BY aNum SEPARATOR " ")
FROM (SELECT SomeId, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(" ", SomeText, " "), " ", aNum), " ", -1) AS SomeWord, aNum, item_title_default
FROM SomeTable
CROSS JOIN (SELECT 1 + a.I + b.i * 10 + c.i * 100 AS aNum FROM integers a, integers b, integers c) SubInt
WHERE aNum <= (LENGTH(SomeText) - LENGTH(REPLACE(SomeText, " ", "")) + 2)
AND LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(" ", SomeText, " "), " ", aNum), " ", -1)) > 3
ORDER BY SomeId, aNum) Sub1
GROUP BY SomeId

这依赖于整数表，该表具有一个称为i的单列，其行的值介于0到9之间.它可以处理多达1000个单词，但很容易扩展.

This relies on a table of integers with a single column called i, with the rows with values 0 to 9. It copes with up to 1000 words but is easily extended.

编辑-好的，这是一个MySQL存储过程，用于拆分字符串并删除长度不超过3个字符的任何单词.再次只在空格处分割.

EDIT - OK, here is a MySQL stored procedure to split the string up and remove any word 3 or less characters long. Again only splits on spaces.

DELIMITER //

CREATE FUNCTION `func_Remove_ShortStrings`(InStr LONGTEXT) RETURNS LONGTEXT
    DETERMINISTIC
BEGIN

    DECLARE RetStr LONGTEXT DEFAULT "";
    DECLARE TempStr LONGTEXT DEFAULT "";
    DECLARE aWord LONGTEXT DEFAULT "";
    DECLARE WordLength INT DEFAULT 0;
    SET TempStr = TRIM(InStr);
    WHILE (LENGTH(TempStr) > 0) DO
        SET aWord = SUBSTRING_INDEX(TempStr, " ", 1);
        SET WordLength = LENGTH(aWord);
        IF (WordLength > 3) THEN
            SET RetStr = CONCAT(RetStr, " ", aWord);
        END IF;
        SET TempStr = TRIM(SUBSTRING(TempStr, WordLength + 1));
    END WHILE;

    return TRIM(RetStr);
END

这篇关于Mysql函数删除少于3个字符的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！