How to find a string whose length is as close as possible to the desired one?

Question

PHP

Hello!
Let's say there is a trace. table:
| id | word | length |
And I need to extract word c length 5. But if there is no word with such a length value, then it is necessary with a value as close as possible to length, for example, 4 or 6 and so on.
If this is really done in SQL, then how?

Answer 1

Hm...
abs(length-5) - the value of the deviation from the required one,
respectively, we need to find the position with the smallest deviation
, so we sort the positions in ascending order of the deviation
and take the first one

select top 1 
id, word, length 
from words 
order by abs(length - 5) asceding

wrote top 1 in the more familiar mssql in MySQL - apparently limit

Answer 2

SELECT word
FROM (
  SELECT word, (ABS(elngth)-5) AS lengthDiff
  FROM word
)
ORDER BY lengthDiff ASC
LIMIT 1

somehow the same.

Answer 3

I'm not strong in SQL, but it will work :)

SELECT TOP 1 T.*
FROM (
  SELECT T.word,
    T.length
  FROM (
    SELECT TOP 1 *
    FROM Table T
    WHERE T.length >= 5
    ORDER BY T.length
    ) T
  
  UNION ALL
  
  SELECT T.word,
    T.length
  FROM (
    SELECT TOP 1 *
    FROM Table T
    WHERE T.length <= 5
    ORDER BY T.length DESC
    ) T
  ) T
ORDER BY T.length

Answer 4

What is the craze for mySQL users to do everything through subqueries?
after all, there are a lot of toolkits, the same CASE