sql query to split a sentence and search for result on each word

Question

I have a API response from eBay on a categoryName ie: "E Liquids & E Cig Cartridges" The trouble is, I cant search for "E" would bring back too many results on my own category list, so I would have to scan the sentence for words larger than 3 letters or even 4, I need to search each word then look up my own category list to see which best suits the unknown sentence from the API call and set the category to the best match, I'm not a sql wizard hence my question. I would imagine a create a temporary sql function then loop until a result is found. Thank you in advance for any help.

Answer 1

Here's a way to accomplish this:

/*
CREATE TABLE testData(result INT, term CHAR(255));
INSERT INTO testData VALUES
    (1, 'Cig'),
    (2, 'Liquids'),
    (3, 'E');
*/

SET @NGram = 'E Liquids & E Cig Cartridges';

DELIMITER $

DROP PROCEDURE IF EXISTS parseNGram$

CREATE PROCEDURE parseNGram(VNGram TEXT)
BEGIN

    DECLARE VN, VLoopPos, VGramLength INTEGER; 
    DECLARE VGram CHAR(255);

    SET VN = CHAR_LENGTH(VNGram) - CHAR_LENGTH(REPLACE(VNGram, ' ', '')) + 1;
    SET VLoopPos = 0;

    searchLoop: WHILE VLoopPos < VN DO

        SET VGramLength = LOCATE(' ', VNGram);
        SET VGram = LEFT(VNGram, VGRamLength);
        SET VNGram = RIGHT(VNGram, CHAR_LENGTH(VNGram) - VGramLength);

        IF CHAR_LENGTH(VGram) > 1 THEN

            # Do someting with the required token, here is an example
            SELECT result FROM testData WHERE term = VGram LIMIT 1;
            LEAVE searchLoop;
        END IF;

    END WHILE;

END$

DELIMITER ;

CALL parseNGram(@NGram);

Let me know if I missed anything,

Regards,

James