Saturday, April 19, 2014
Text Size

MySQL: Find all non-alphanumeric rows

Quick note on how to do this.

I was tasked with cleaning up an english database by replacing all special alphabets (ë to e) and non-alphanumeric symbols with URL friendly characters.


  1.  -- return all records that contain non-alphanumeric characters 
  2.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'
  4.  -- return all records that are non-alphanumeric but ignore underscores 
  5.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\_]+$'

SQL: Use CASE for Relevance column

So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a column of relevance and sorts the rows accordingly.

This has to be a real quick one for a dropdown search field which has to find relevant terms to autofill/autocomplete a search form:

  1.  -- where @ThisSearch is a posted (and sanitized) variable 
  3.  SET @ThisSearch:="Brains"; 
  5.  SELECT 
  6.          columnID, 
  7.          columnFullName, 
  8.          CASE 
  9.                  WHEN columnFirstName LIKE @ThisSearch THEN 20 
  10.                  WHEN columnFullName LIKE @ThisSearch THEN 10 
  11.                  WHEN columnLastName LIKE @ThisSearch THEN 10 
  12.                  WHEN columnFullName LIKE @ThisSearch THEN 1 
  13.          END as relevance 
  14.  FROM 
  15.          myTable 
  16.  WHERE 
  17.          s.columnPublished <= NOW(
  18.          AND ( 
  19.                  SOUNDEX(@ThisSearch)=columnFullSoundex 
  20.                  OR columnFullName LIKE @ThisSearch 
  21.          ) 
  22.  ORDER BY relevance DESC, columnFullName ASC 

Reorder Columns in a Table

Just a quick note to myself on how to reorder columns as I was having difficulty using a phpMyAdmin interface to do this.

Taken from the best forum for programming Qs&As:

Method: phpMyAdmin
So in the phpMyAdmin interface, apparently, you can drag the columns by clicking and holding on the headers when viewing a table structure... Though maybe this is derived from a template and depending on if you are using MS Internet Explorer...

Can't get it working? I use whatever is most useful and Google's Chrome is the fastest browser I have. Here are some ways to do this:

Export/Import Database using MySQL Workbench

This article isn't unique as there are other tutorials out there. This is meant to detail a process to export a database using MySQL Workbench and then to import it into another database.

If you're confined to use this product then this is how to do it. Personally, even a web-based app such as PhpMyAdmin would fare better.


Quickly update all content in mySQL database replacing a string

This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change:

Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Fri 28-Mar-14
      You are a rockstar mate! thanks. Followed the steps listed and it worked! If only all tutorials were ...
  • K2 Items disappear

    • Thu 03-Apr-14
      The fix works great, but the problem is occurring a couple of times a day. Any idea how to fix ...
      Larry C.