MySQL – Select First 2 Characters In Field Using Matching Pattern

Problem: After running Easy Profile on Joomla to split up Joomla’s standard First-Name-and-Last-Name-all-on-one-line into separate First Name and Last Name fields, a bunch of User’s middle initials (eg: L.) are now at the beginning of the Last Name field.

Here’s an example: Joomla’s standard signup only provides one field to enter a user’s name: John L. Smith. We need First and Last Name in separate fields. So, Easy Profile to the rescue. Except now John’s name is a little bungled.

First Name field: John
Last Name field: L. Smith

MySQL – Select First 2 Characters In Field Using Matching Pattern

This is not Easy Profile’s fault. It’s just the way the algorithm works.

Solution: Run SQL Matching Pattern to SELECT only the records that have a “first-letter” + “.” in the Last Name field (ie: the L. in John L. Smith):

SELECT * FROM xxxx_jsn_users WHERE lastname LIKE '_.%'

MySQL Matching Pattern explanation:

  • Use LIKE instead of = or <>
  • Use _ (underscore symbol) to match any single character (like the L. in John L. Smith). Since there are hundreds of names with different middle initials, the _ (underscore symbol) will catch all letters.
  • % (percentage symbol) matches an arbitrary number of characters, because I also want to show everything after the middle initial (eg: L. Smith, Ph.D., M.D., J.D.).

Using John L. Smith as an example:

  • _ catches all letters A-Z; followed by
  • . (“.”); followed by
  • %
  • Which equals ‘_.%’ in the SELECT statement

Note 1: Change xxxx to your database’s prefix_
Note 2: Before running the script, you may need to re-type the quotations after the script has been copied-and-pasted into the database SQL panel.

DELETING the “middle initial” + “.” – while leaving the Last Name intact – I have yet to figure out.

Helpful source: MySQL Pattern Matching

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *