Find all records where first_name column does NOT start with A-Z

Question

I need to extract a subset of results where the value for the first_name column starts with anything other than standard English alphabet letters (i.e. A-Z), for example È. I am banging my head against a brick wall with this - for example, this is how I've been able to get all the records starting with the letter A:

@results = User.where("first_name LIKE ?", "A%").order("first_name ASC")

Any help would be wonderful.

EDIT: I am using a PostgreSQL database.

Answer 1

You can use ~ operator to match regular expression. ~ is used to match case sensitive data. So in your case first_name ~ '^[^A-Z]' will give users whose first_name doesn't start with A-Z. To match case insensitive you need to use ~*.

  @results = User.where("first_name ~ ?", "^[^A-Z]").order("first_name ASC")

or

You can also use !~ which gives the unmatched results (i.e.) first_name !~ ^[A-Z] equivalent to first_name ~ ^[^A-Z]. To match case insensitive use !~*.

  @results = User.where("first_name !~ ?", "^[A-Z]").order("first_name ASC")

For more information see documentation

Answer 2

This should work in Postgresql

@results = User.where("first_name SIMILAR TO ?", "[A-Z]%")

More info here.