How to query for strings based on their length

Question

create table dict {id, word varchar2(255)).

I store strings of varying lengths within the 'word' field. How do I query for the following cases:-

1. Search all words which are 4 characters in length
2. Search all words which are greater than 4 characters in length
3. Search all words which have 4-8 characters (inclusive)
4. Search all words which are lesser than 4 characters in length

Env: h2, mysql. The data consists of english words, but would like to know the limitations if you use multibyte data (e.g japanese)

Answer 1

SELECT
  *
FROM
  dict
WHERE
  LENGTH(word) = 4 /* 1. */
  OR (LENGTH(word) > 4) /* 2. */
  OR (LENGTH(word) >=4 AND LENGTH(word) <= 8) /* 3. */
  OR (LENGTH(word) <4) /* 4. */

/* Choose the right WHERE clause, based on your case */

For multibyte data, you should use CHAR_LENGTH instead of LENGTH

Answer 2

You could refine the question and explain what was not clear in the documentation for string functions.

CHAR_LENGTH() docs state clearly that

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.