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
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
