Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

php mysql search

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.
0
the-miz
Asked:
the-miz
  • 3
1 Solution
 
Ray PaseurCommented:
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.
0
 
the-mizAuthor Commented:
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
0
 
Ray PaseurCommented:
I think the cleaner way would be to alter the table.  That is what I would probably do.
0
 
maeltarCommented:
Something like :

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

Open in new window


0
 
Ray PaseurCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now