php mysql search

Posted on 2011-10-20
Last Modified: 2012-05-12
I have written code to list all companies in our database alphabetically.  I have quick links at the top from A-Z and also have one other link that is suppose to get all companies that start with a number or special character.  Does anyone know the SELECT command in MySQL that can search all the companies and list them that begin with a number or special character.  Basically a list of all companies that DO NOT start with a letter.
Question by:the-miz
    LVL 107

    Accepted Solution

    Possibly you could use something like SELECT mycompany FROM mtable WHERE mycompany NOT IN (SELECT mycompany FROM mtable WHERE mycompany LIKE ' a%' OR 'b%' OR 'c%'   ... etc

    But I think it might be easier to use ALTER TABLE and add a selection column.  You could use the first letter of the company name to load the column, or you could load the column with '1' to indicate a non-letter name.

    Author Comment

    Yes, I thought of those two ideas.  Using the OR statement in the clause will get long, for each letter in the alphabet.  Thought there might be a cleaner way
    LVL 107

    Expert Comment

    by:Ray Paseur
    I think the cleaner way would be to alter the table.  That is what I would probably do.
    LVL 11

    Expert Comment

    Something like :

    SELECT * FROM tableName WHERE NOT columnToCheck REGEXP '[A-Za-z0-9]';

    Open in new window

    LVL 107

    Expert Comment

    by:Ray Paseur
    That WHERE NOT REGEXP might need to be something like '[A-Za-z]{1}.*?' to find the companies like 3-M that start with numbers.  Not quite sure about that regex syntax in MySQL.  I still think I might change the table to add a selection column.  It would depend on the weight of the table scan that would result from the SELECT .. WHERE NOT.

    Best to all, ~Ray

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now