CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlpattern-matching
AnApprentice
AnApprentice

Reputation: 111000

How to query for non A-Z entries?

I have a table with a field title. I currently query by letter as follows:

Group.where("title like ?", "#{@letter}%")

This works great for a-z.

What can I pass to @letter to get all rows that do not start with a-z? Entries that start with '1', or '500' or some other non a-z character for example?

Upvotes: 2

Views: 8511

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657617

You need a regular expression match for that in PostgreSQL:

SELECT *
FROM   tbl
WHERE  title ~ E'^\\w.*';

\w being the class shorthand for [[:alnum:]_]. Note that this only includes digits and the underscore _. Or:

title ~ E'^[^a-zA-Z].*'

.. to match all characters except a-z and A-Z at the first position.

You cannot do that easily with LIKE.

Upvotes: 3

Justin Pihony
Justin Pihony

Reputation: 67105

I believe that this will work: [^a-z]

I tested it on my own temp table and it worked fine for me:

select * from #test where test like '[^a-z]%'

Upvotes: 1

Related Questions