Select where first letter in a range ( PostgreSQL )

Question

I am trying to select from a table where word's first letter is in a range (a-f for example)

I tried with a where clause like this:

WHERE lower(substring(title from 1 for 1)) IN ARRAY['a', 'k', 't']

hoping that I will find a way to generate the range ARRAY dynamically later.

The above is not working. Any idea what I'm doing wrong?

Answer 1

You can use the SIMILAR TO keyword. The following will match all titles that start with either 'a', 'k', or 't'.

... WHERE lower(title) SIMILAR TO '(a|k|t)%'

If you want to use a range, you could use the [] notation:

... WHERE lower(title) SIMILAR TO '[a-f]%'

NOTES

1. The % character matches any number of characters following the pattern. For instance, the second pattern example would match: 'abc', 'ab', 'a', 'far', 'fear' etc.

2. Also, it is important to note that the SIMILAR TO keyword is only available to PostgreSQL and it is not ANSI SQL.

3. Finally, the lower(title) is not necessary when using the character class. You could simply search for something like

   WHERE title SIMILAR TO '[a-fA-F]%'

Answer 2

IN doesn't understand an array on the right side, you want = ANY:

WHERE lower(substring(title from 1 for 1)) = ANY (ARRAY['a', 'k', 't'])

Or you could use LIKE, SIMILAR TO, or ~ (POSIX regex).

Extra references:

• Subquery Expressions
• Row and Array Comparisons