Whitespace with SELECT WHERE LIKE

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I have a SELECT statement looking for any match in last name.

Code:

$sql = $db->sql_query("SELECT * FROM ".$prefix."_tl_students WHERE Name_Last LIKE '%$Scantron_Name_Last%'");

The record in the imported scantron may be

MCGREGOR

Problem is if a Students last name in the student table has a space in it I don't get a match.

For example in the student table:

Mc Gregor

how could I edit my code to take this into account?

The student table will eventually be university wide and I won't be able to edit it in the future. I will need to change how I look at the imported scantron data. I assume the software they use when the send these cards thru a card reader does not take into account any whitespace on the scantron. They treat it all as one word, or name.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

You could try
WHERE REPLACE(Name_Last,' ','') LIKE '%$Scantron_Name_Last%'")

Or in the event you run up against other data issues
you could try
WHERE SOUNDEX(Name_Last) LIKE '%$Scantron_Name_Last%'");

Posted: Wed Jan 09, 2008 1:40 pm

Thanks