Select longest word from a string SQL

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?

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)