Let’s say you’re at work, and you need to find something in a CRM (Customer Relationship Management) application. You only partial information, say the customer’s last name or perhaps part of the street address. You enter that information in, but can’t seem to find the customer. Your only option appears to be to thumb through thousands of entries.

The technical problem behind this is the people who designed the software didn’t account for partial search terms. It happens in more applications than you might think.

The secret to finding nearly anything in applications like these is a simple percent sign (%) – especially if the application you are using is searching through a database.

Databases are searched (using a query) in a computer language called SQL. (Pronounced S-Q-L, or “sequel”). And SQL is kind of picky when you search. For example, if you search for ‘Kim’ in the first name field, the database will return anything that is ‘Kim’ in the first name field. But, it will not return ‘Kimberly’, ‘Kimm’, ‘Kimmy’, ‘Hakim’, etc. Just Kim.

You have to specifically tell the database that when you search for ‘Kim’, you want any first name that has ‘Kim’ in it. You do this with the percent sign. If you place a percent sign after the name, it will return anything that starts with ‘Kim’. So, searching for ‘Kim%’ will give you Kim, Kimberly, Kimm, and Kimmy.

How do we get Hakim though? You guessed it. Add a percent sign to the front. So ‘%Kim%’ will return anything in the first name field that has kim anywhere in the word.

So, how do we apply this to our search?

If the application doesn’t automatically place the percent signs in, you can add them in yourself. Say you wanted to search for all customers who live on Parkplace Lane. You don’t know whether they put their address as Parkplace Ln, Parkplace Lane, Parkplace, etc. Enter the following in:  %Parkplace%. This will return everyone who has the word Parkplace in their address. If you want to further design your search, enter %Parkplace L%, as this will return everyone with Parkplace L somewhere in their address. This covers Lane, Ln, L, Lne, and any other derivative. It eliminates those who don’t live on Parkplace Lane.

Here’s another example. Let’s say you are searching for Tom Smith, but you don’t know if Smith is spelled Smith, Smyth, smeth, Smythe, or even misspelled as smth. Entering ‘Tom Sm%’ will return everyone whose name is Tom Sm plus anything else after.

Another secret – if your application allows it you can return every record by entering just a percent sign in the search. So just typing in ‘%’ will tell the database to return all records.

The last example illustrates a danger, and one of the reasons some application specifically don’t let you use the percent sign. When use incorrectly, the search can easily overload the application and database and possibly cause it to crash. Smart application designers limit the results without limiting your ability to search.

How do you know if you can actually use this trick? Open the application’s search screen, and search for something you know is there. For example, enter Tom Smith. See if results are returned. Now, search for ‘Tom Sm%’ and see if you get at least the same results as you did before. You may get more, as expected. Try ‘%Smith%’. If it returns anyone with a last name of smith (there may be a lot) this strick will work.

Happy searching!