Thursday, July 24, 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

  • Joes Quicklist Weblinks (JQW)

    • Thu 17-Jul-14
      Hi, thanks a lot. But I have got a problem with download it. Error: "File could not be found Important!
    • Tue 15-Jul-14
      Hi Artur, I forgot to get back to you sooner. I was going to include a set of instructions to do this ...
    • Mon 14-Jul-14
      Hi Skarosg, Not sure if we took this to e-mail but I noticed your site is displaying the images now.
    • Fri 11-Jul-14
      Hi, How can I put user name and category of weblink in module, near the date? Thanks..
  • Joes Word Cloud (JWC)

    • Tue 17-Jun-14
      I'm using JE 3.1.19 stable.