Searching for part of a column value
Say I have a Name column in my table. This column is in the format "Lastname, Firstname". Now say I want to search my SQL Server database for the last name, and I want all records with the word "Johnson" in the Name column?
I was thinking something like Select * FROM Table WHERE Name LIKE [*$PHPSearchString*]
Gil Hildebrand, Jr.
Internet Consultant
New Orleans, LA
Peter J. Boettcher posted this at 17:11 — 14th December 2000.
They have: 812 posts
Joined: Feb 2000
Gil,
It would be easier (and a little more efficient) if you setup the names in two separate fields (Like First_Name & Last_Name), why waste resources searching through text you don't need?
Your select statement looks pretty good, except for the fact it won't distinguish between first name & last name. So, in your example a search for the last name of Johnson could return the following recordset:
Johnson, Bill
Johnson, Steve
Smith, Johnson
Since your select statement is searching the whole field it could return a recordset like this. I would strongly recommend separating this fields, it will make life much easier. If you can't then you'll have to parse the search query and build some logic into it (ignore everything after ",").
PJ | Are we there yet?
pjboettcher.com
Gil posted this at 22:13 — 14th December 2000.
They have: 103 posts
Joined: Apr 1999
Thanks Peter. I was having some problems with that Select statement earlier but now I have it working. For what I'm doing it's fine if it picks up Johnson, Bill and Bill, Johnson. I think I was trying to do LIKE=[*$foo*] earlier...
Gil Hildebrand, Jr.
Internet Consultant
New Orleans, LA
Gil posted this at 00:44 — 15th December 2000.
They have: 103 posts
Joined: Apr 1999
It needs to be this:
Select * FROM Table WHERE Name LIKE '%$PHPSearchString%'
It's amazing what you can find when you look at documentation...
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.