PDO搜索查询

Question

PDO, I want to use a search query. I tried to use like query, using % on both front and end of search variable. but it search in between the words.

like if in database there is name Jhon Anderson and if i use search variable using LIKE %erson%, The query will get me the row which contains the above name. But what i want is query should only search with starting alphabets so i tried the LIKE query with Jhon%, i got the desired result, but using this % in the end only works if search variable matches the starting of first word, but what about the word after the space?

I mean if i try Anderson% or Ander%, The query will not get me the result of this row as Anderson is the second word in the field Jhon Anderson after space.

So my question is how to search in the words after spaces, that if there starting alphabets match the search query.

I don't know if such kind of query exist or not, but i think it might be possible with some looping after using like query with wildcard '%'.$variable.'%' and after getting all the results unset the arrays or rows where search variable do not match with the first alphabet of the result.

Any ideas how to implement such kind of search.

Query right now i am using,

$stmt = $conn->prepare("SELECT ROOM, GUEST_NAME, GUEST_FIRST_NAME, CONFIRMATION_NO, DEPARTURE, PWD FROM RESERVATION_GENERAL_2 WHERE LOWER(GUEST_FIRST_NAME) LIKE ? OR LOWER(GUEST_NAME) LIKE ?");
$stmt->execute(array('%'.strtolower($searchFilter).'%','%'.strtolower($searchFilter).'%' ));

Answer 1

A bit dirty, but simple solution is to include the space in the search:

WHERE ' ' || Name LIKE '% Anderson%'

You see I added a space in front of Name, so it will also find the word if it is at the beginning of the string.

Alternatively, you can use REGEXP_LIKE, but then it is still a bit awkward, since Oracle doesn't have a word boundary expression in its regex engine. For a solution, see this answer