MySQL SELECT first letter of the words sequentially for searching

Sample words:

iknkr st nm krt prk
iknkr vhgr j k pth  
iknkr hkm st
iknkr sr vhgr j k pth

Required Condition

Now, if a user types: ik s the query will return:

iknkr st nm krt prk 
iknkr sr vhgr j k pth

if a user types: i v the query will return:

iknkr vhgr j k pth

Currently Trying:

SELECT `walpha` AS word, pageID AS id 
FROM `SX01` 
WHERE `table`= 'S01' 
  AND `walpha` like '" . $this->db->escape_like_str($searchVal['keyword']) . "%' 
LIMIT 100"

But using this user have to type: iknkr s

to get the result

iknkr st nm krt prk 
iknkr sr vhgr j k pth

Can anyone suggest, what will be the right way of doing it.

Answer

You could build a regular expression from the input and then use REGEXP to have MySQL use it. For example, you could turn ik s into ^ik[a-z]* s to match words which which start with ik followed by a word starting with s

SELECT words FROM tbl WHERE t.words REGEXP '^ik[a-z]* s';
  • ^ means we must match from the start of the string
  • ik must then be the first two chars
  • [a-z] will match any lowercase alphabetic char
  • * allows us to match zero or more of the previous pattern – i.e. any sequence of chars can follow ik
  • then must have a space
  • s followed by an s – we don’t care about what follows.

You can build this pattern from the user input by replacing all spaces with [a-z]* and prefixing with ^