MySQL's LEFT() Function

Album Cover: The Bends

"All your insides fall to pieces; you just sit there wishing you could still make love."
Radiohead / High and Dry

Posted on December 27, 2004 1:18 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

While evaluating an unfortunate, massive comment-spamming that has taken place on my Coldplay site, I managed to learn something cool. In MySQL you can use the LEFT() string function to select a set number of characters from the left of a particular data column. This allows me to take a look at the first few words in a comment to evaluate whether it is spam or not. In cases where the commenter's name doesn't quite give it away, this is very useful.

So for example, the following query gives the displayed results:

SELECT id, name, LEFT(comments, 25) FROM comments ORDER BY cdate DESC LIMIT 5;

| id   | name         | LEFT(comments, 25)        |
| 1003 | phentermine  | <h1>In your free time, ch |
| 1002 | online poker | <h1>Please check out the  |
| 1001 | phentermine  | <h1>You can also visit so |
| 1000 | online poker | <h1>You may find it inter |
|  999 | mortgages    | <h1>In your free time, ch |

For more examples of MySQL's cool string manipulation functions, be sure to check out the MySQL Manual.


Arcanius on December 27, 2004 at 9:29 PM:

Bernie, I'm sorry to hear that the comment spammers have gotten to you. Aside from the fact that I think MySQL is kinda lame, when compared to solutions like PHP 5's built-in SQLite (which I have never used, so maybe I'm just blowing smoke), this LEFT() function sounds like a pretty useful feature. In case I haven't mentioned this enough when talking about why I think the SQLite solution for databases is so much better is because it makes the site much more portable. The database is just another file on the web server; no additional program is needed. If you actually need the ful scope of a database application, MySQL is a poor choice compared to, for example, PostgreSQL (once againm, I haven't used PostgreSQL myself, maybe I'm wrong). This evalution is based not on experience, but on the shortcomings of MySQL is places such as subselects, and for a lightweight solution, it is not nearly as lightweight as SQLite. But back to my main point, comment spamming. I have been quite successfully thwarting comment spammers by adding a "I Am Human" checkbox next to the commenter's name. The setting is remembered in a cookie as well, so it is checked the next time a real commenter comes back. This makes it very easy for my real commenters, and has blocked all attempts at comment spam so far. I encourage my friends to try similar solutions (it is a drop-in two-file replacement for Wordpress). Of course, this solution relies on the fact that I am one of only a handful implementing it, so don't let it get too far out of the bag :-).


Bernie Zimmermann on December 28, 2004 at 12:48 AM:

Arcanius, it's funny that you brought all of this up because I was planning on posting (and probably still will) about some of the shortcomings of MySQL and how they apply to the issues I've been facing with comment-spam.

When it comes down to it, though, I wouldn't trust a database system like SQLite to run my blog and especially not a more popular site like my Coldplay site. For one, SQLite integration with PHP is a new thing, and that brings its own set of caveats. Secondly, a lot of the things the SQLite folks sacrificed in order to live up to the "lite" monniker are things that I look for in a good database system.

If MySQL wasn't an option, I would most certainly use PostgreSQL as the backend to my blog, my Coldplay site, and any other sites that required a database backend. I've used Postgres before and am very pleased with the feature set offered. The only things I'm underwhelmed by are the lack of a good, consistent source of documentation (they've actually come a long way since I last used their database system) and ease-of-use from the command line. Sure, you can get used to anything...look at Vi, for instance...but I've found that after you've stepped away for a while, remembering something like "show tables" (MySQL) is a lot easier than remembering dp (Postgres). All-in-all, I hate to say anything bad about Postgres because I've considered using it even with MySQL as an option, but when it boils down to it I continue to pick the latter every time.

MySQL has a command-line interface that is easy to remember. Important maintenance tasks like database dumps and backups are a snap. 99% of the time when something goes wrong with a MySQL table (and it does happen), a simple "CHECK TABLE" will identify the problem, and a "REPAIR TABLE" will fix it. When you're running a big site that carries a lot of useful data and sometimes requires interesting schema changes that don't affect integrity, MySQL has proven (for me at least) to be the best option.

This could get me into a bit of trouble, since comparing MySQL to Postgres is a bit like comparing Firefox and Opera (both very good browsers), but a little controversy is good for you every now and then. I think.


MB on November 27, 2007 at 11:39 PM:

But isn't there one important difference as well: PostgreSQL costs, while MySQL comes free... Surely an important issue if you're a hobbyist!


Sam Hammouri on November 28, 2008 at 12:30 AM:

I know my comment here is a bit belated, but I just wanted to say that I've been using MySQL for 5 years, and featurewise It hasn't let me down once. I certainly am not a guru, I don't know all the functions and commands of MySQL, but on each and every occasion that I've tried to get something done, I could always depend on MySQL to have some command that does the job nicely and efficiently. And I've created some rather complex SQL queries.


Is Mundu on September 15, 2009 at 9:20 AM:

#MB: PostgreSQL costs nothing. In fact, it has a more open license than MySQL.


percocet information on October 25, 2011 at 9:04 PM:

It is a very good thing, All about percocet, rckq,


buy xanax online on October 27, 2011 at 1:09 PM:

Best, buy xanax, :)),


All about percocet on October 30, 2011 at 3:20 AM:
buy adipex on October 30, 2011 at 12:57 PM:
replica rolex watches online on October 30, 2011 at 3:02 PM:

Nice, replica rolex watches online, >:[[,


buy cialis for you on October 30, 2011 at 5:11 PM:

It is a very good thing, buy cialis, =)),


buy xanax on November 16, 2011 at 5:51 AM:

Give somebody the to a site about the, buy xanax here, =-DDD,


Buy xanax online overnight shipping on November 16, 2011 at 9:52 AM:

Best Wishes, xanax online overnight shipping, 029,


Only buy tramadol on November 16, 2011 at 11:18 PM:
Best replica rolex watches on November 17, 2011 at 5:01 AM:

Hi, replica rolex watches, iewj,


buy cialis free on November 17, 2011 at 6:58 AM:

I want to say thanks!, buy cialis, %OOO,


fake coach purses on November 17, 2011 at 2:00 PM:

I like your work!, fake coach purses, bvc,


replica rolex watches on November 17, 2011 at 3:09 PM:

Very interesting sites., Best replica rolex watches, ufgav,


Best free musical ecards on November 17, 2011 at 5:03 PM:

can you do thi for me, Best free musical ecards, =-),


hair transplant on November 17, 2011 at 6:22 PM:

Only, hair transplant, bwpj,


arthritis treatments free on November 17, 2011 at 7:20 PM:

So where it to find, arthritis treatments free, >:-)),


power wheelchair on November 17, 2011 at 9:52 PM:

What is it, power wheelchair, =[[,


panty pics now on November 18, 2011 at 2:30 AM:

Your Site Is Great, panty pics now, fnxoo,


diovan free on November 20, 2011 at 6:55 PM:

I like it so much, diovan, >:-),


buy xanax online on November 20, 2011 at 7:06 PM:

Is it so important?, buy xanax, 1954,


Golf Club Bags on June 03, 2012 at 9:43 PM:

Simple and very nice script ! Thanks !


forex broker reviews on June 04, 2012 at 10:28 PM:

forex broker reviews can give potential currency market traders and investors,more of human insight into the different brokers available.


Dustin James on June 11, 2012 at 6:48 AM:

I have read your post here and i enjoy a lot and learned a lot of things about your blogs! Great post!
"> fretag


top forex brokers on June 12, 2012 at 8:12 PM:

This article is about the top forex brokers in the market.


David on June 14, 2012 at 5:06 AM:

Simple but very nice Script! Thanks!
"> moviestar


top forex brokers for USA on June 17, 2012 at 9:49 PM:

Thank you for taking the time to publish this information! I’m still waiting for some interesting thoughts from your side in your next post thanks visit this site also top forex brokers for USA


JessicaBuzz on September 13, 2012 at 1:01 AM:

Brushing up on the latest gossip with celebrity news websites and blogs is a great way to pass the time. You might even find some great people to talk to or find a new hobby by adding your own content to the sites.


rise health systems on September 26, 2016 at 11:17 PM:

There are typically many approaches to add internet website marketing to your individual dental promotion plan. The web would be the actual gigantic market for a lot of company products. It is important to finish at minimum amount some internet website marketing for your dental corporation.


pet stalk on September 26, 2016 at 11:17 PM:

Choosing the ultimate Pet — 9 Rules With the Decision Pets are typically truly spouse and children. Over 60% regarding American individuals have a minumum of one dog, pet, bird, or perhaps other pal animal. Quite a few have various.


wheel travels on September 26, 2016 at 11:18 PM:

These include the holidaymakers who vacation on that you season so that you can participate in the sports. With regards to ex: Travelers who’re all gonna Christmas Winter sports or bungee soar or almost any skiing for example. They have to carry acceptable medications together with fully wanting to avoid the exact injuries.


Dofollow Niche Relevant blog comments on October 03, 2016 at 11:33 PM:

I really impressed after read this because of some quality work and informative thoughts . I just wanna say thanks for the writer and wish you all the best for coming!.


MySQL commands on November 29, 2016 at 7:21 PM:

LEFT() in MySQL is very useful!


Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.


Email Address:



Check this box if you hate spam.