Let’s say that I have a table with 2 columns: name and number. Name is a string which can have one or more words and I would like a new query with select name number but that in the name column it only has the longest word of the original table.
Is there a function in SQL that extracts from a string only the longest word?
Advertisement
Answer
This procedure return the first longest word of the phrase pass like parameter.
delimiter // CREATE PROCEDURE getLongestWord(IN phrase VARCHAR(255), OUT w VARCHAR(255)) BEGIN DECLARE i INT DEFAULT 1; DECLARE maxIteration INT DEFAULT 1; DECLARE wordIndex INT DEFAULT 1; DECLARE maxLength INT; DECLARE proxLength INT; SELECT LENGTH(phrase) - LENGTH(replace(phrase,' ','')) + 1 INTO maxIteration; SELECT LENGTH(SUBSTRING_INDEX(phrase, ' ', i)) INTO maxLength; WHILE i < maxIteration DO SET i = i + 1; SELECT LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(phrase, ' ', i), ' ', - 1)) INTO proxLength; IF (maxLength < proxLength) THEN SET maxLength = proxLength; SET wordIndex = i; END IF; END WHILE; SET w = SUBSTRING_INDEX(SUBSTRING_INDEX(phrase, ' ', wordIndex), ' ', - 1); END // delimiter ;
To use it:
CALL `getLongestWord`('one two three four five six seven eight nine ten', @p1); SELECT @p1 AS `w`;
return: ‘three’ (because the longest words have 5 letters and this is the first of them)