I have a table in postgres database where I need to find all the rows -

• Between two dates where fromTo is date column.
• And also only those rows where column data contains mix of lower and upper case letters. for eg: eCTiWkAohbQAlmHHAemK

I can do between two dates as shown below but confuse on second point on how to do that?

SELECT * FROM test where fromTo BETWEEN '2022-09-08' AND '2022-09-23';

Data type for fromTo column is shown below -

fromTo          | timestamp without time zone |           | not null | CURRENT_TIMESTAMP

CodePudding user response：

You can use a regular expression to check that it is only alphabetical characters and at least one uppercase character.

select *
from foo
where data ~ '[[:upper:]]' and data ~ '^[[:alpha:]] $';
  and fromTo BETWEEN '2022-09-08' AND '2022-09-23'

The character classes will match all alphabetical characters, including those with accents.

Demonstration.

Note that this may not be able to make use of an index. If your table is large, you may need to reconsider how you're storing the data.