Have you ever needed to find something in a SQL table that you couldn’t quite remember the name? You know a certain part of it, or think you do, but you don’t know the whole thing? Maybe you want to be sure that you have changed all of a certain name in the table? Well, here’s how:

In SQL, there is this nifty concept of a “like” search, also many times called a “contains” search. What it does is gives you things “like” whatever you tell it.

Let’s say you have a table full of popular Tweets (twitter messages) and you want to find all the ones that have “Bieber” in them. Now I have no idea why you would store tweets in a database (there are usually much more elegant ways) especially ones that are related to Justin Bieber. I guess you would if you are a big fan. I’m not, I only added this to see if Bieber is good for my website traffic, as I have heard it is. Lil test for myself right here.

You could find all those tweets with “Bieber” somewhere in the tweet field by doing this:

SELECT tweet from tweets WHERE tweet like ‘%bieber%’

The ‘%searchtermhere%’ portion is the real magic, because it tells SQL to find a text string (inside the quotes) and the % (percents) are what tell it to find anything before and after the search term.

Of course you could leave off one or both of the % signs, to get only those matches in the field that have nothing before your term, and/or nothing after your term. So:

–this will only give you a list of results that have bieber at the very beginning of the text in that field. If there are matches that have any text BEFORE bieber, they won’t be included.
SELECT tweet from tweets WHERE tweet like ‘bieber%’

–this will do just the opposite, giving you only matches in the field that have something before bieber, but not after it.
SELECT tweet from tweets WHERE tweet like ‘%bieber’

You can remove both % to get only matches that the whole field’s contents are simply bieber.

You can also use these methods to find parts of words, especially if you don’t know how it is spelled. Simply replace bieber with bie or ber and it should still find it, though you might get things you don’t expect to. You can always find a correct spelling and rerun the query with the longer word to narrow it down. Works like a charm.

Hope this helps and makes sense, but if not, let me know and I will fix it or improve it. Thanks!