asked on

Alphabetic Pagination Results

Why can't we write an efficient query to check if any results are not started with a set of alphabets? I am using this https://wordpress.org/plugins/alphabetic-pagination/ but i don't know that how can i check if rest of the alphabets are not having any results in it.

Steve Bink

You can, but the way to do it in vanilla MySQL is a bit annoying and inefficient.

First, if you're pulling all the results at once, you can create a calculated field in the query with just the first letter of your result key. This gives you an easy way to identify alphabet membership.

If you're willing to use a pre-query count, then you can do something like:

SELECT SUBSTRING(`myfield`,1,1) as FirstLetter, COUNT(*) as entry_count FROM mytable GROUP BY FirstLetter

Open in new window

If you want to SELECT records which start with specific characters, you can use the LIKE or REGEXP operators. For example, to find all entries which do not begin with a letter:

 SELECT * FROM `mytable` WHERE `myfield` REGEXP '^[^A-Z]'

Open in new window

Keep in mind that REGEXP is not case-sensitive, unless you're dealing with binary strings.

Finally, if you want to get really fancy, check out lib_mysqludf_preg. This is a plugin for MySQL which leverages the full strength of the standard PCRE library. It introduces five functions, with which you can use standard regex to search and/or replace. It installs easily on 'nix-based systems, though it does require some patience for Windows.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.