MySQL SELECT first letter of the words sequentially for searching

Question

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.

Can you explain the logic? if user type `ik` you want words that contain or start with `ik` ? — Juan Carlos Oropeza, Feb 23 '17 at 14:06
start with `ik`, but search will start from first word, if a user type `ik s vb n`, then it will match `ik` from first word, `s` from second word, `vb` from third word and `n` from fourth word, and show the results based on that — Suraj Gupta, Feb 23 '17 at 14:08
So if two words like `ik s`, first word need to start with `ik` and second word with `s` ? — Juan Carlos Oropeza, Feb 23 '17 at 14:08
@JuanCarlosOropeza OP wants that each string user inputs is matched against a word in his strings. So "AA BB" means that first word in the string must start wit AA and the second with BB. this at least is my best guess on this confusing post — Lelio Faieta, Feb 23 '17 at 14:09
@LelioFaieta You dont have to guess. If question isnt clear you wait for OP to clear the issues, otherwise you waste your time answer the wrong question ;) — Juan Carlos Oropeza, Feb 23 '17 at 14:10
@JuanCarlosOropeza I'm on coffee break and trying a new kind of crossword puzzle ;) — Lelio Faieta, Feb 23 '17 at 14:12
@LelioFaieta Also you're completly write, its what he's asking for. — Twinfriends, Feb 23 '17 at 14:13

Paul Dixon · Accepted Answer · 2017-02-23T14:25:03.310

3

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 ^

edited Feb 23 '17 at 14:25

answered Feb 23 '17 at 14:11

Paul Dixon

295,876
54
310
348

regular expression are very hard for me. Why you dont have to include `s*` to indicate anything else? – Juan Carlos Oropeza Feb 23 '17 at 14:14
@JuanCarlosOropeza : the `*` signs indicates anything after ik and `[a-z]` means it can be any alphabetical chars . query is working fine – prakash tank Feb 23 '17 at 14:16
`s*` would mean 'zero or more s chars' which isn't what you want. You just want to be sure the next work starts with s, and not care about the rest. – Paul Dixon Feb 23 '17 at 14:16
@prakashtank I understand the first `*` my question is why `s` doesnt need `*`. – Juan Carlos Oropeza Feb 23 '17 at 14:18
OK, I got it now. Is different from DOS command like `dir myfile.*` – Juan Carlos Oropeza Feb 23 '17 at 14:18
The problem with this answer is how you use user input to update your query? – Juan Carlos Oropeza Feb 23 '17 at 14:21
break the input on spaces and build the regex yourself. Exercise for the reader :) You could build the regex with MySQL string replacement and concatenation operations too. – Paul Dixon Feb 23 '17 at 14:22
1

I am able to resolve the issue using your suggestion - `"SELECT \`walpha\` AS word, \`pageID\` AS id FROM \`SX01\` WHERE \`table\`= 'S01' AND \`walpha\` regexp concat('^', replace('" . $this->db->escape_like_str($searchVal['keyword']) . "', ' ', '[a-z]* ')) LIMIT 100"` +1 @PaulDixon – Suraj Gupta Feb 23 '17 at 14:53

fthiella · Answer 2 · 2017-02-23T14:18:15.737

1

If I understand your logic correctly, you could try with a query like this:

select `walpha` AS word, pageID AS id
from `SX01` 
where
  `table`= 'S01' 
  and `walpha` like concat(replace('" . $yoursearchval . "', ' ', '% '), '%') 
limit 100

for example:

concat(replace('i k', ' ', '% '), '%')

will become:

i% k%

but if you want the words to be consecutive, you'll have to use a regular expression:

where whalpa regexp concat('^', replace('i k', ' ', '[^[:space:]]+ '))

and i k will become:

^i[^[:space:]]+ k

the string has to start with i
after i there has to be at least + one non space character
then one space
then one k
then anything else

edited Feb 23 '17 at 14:18

answered Feb 23 '17 at 14:13

fthiella

48,073
15
90
106

That would match `iknkr zzzz ssss` which might not be what the OP wanted - it doesn't ensure that the *second* word will start with s – Paul Dixon Feb 23 '17 at 14:15
@PaulDixon yes you're right, i've just updated my answer, thank you – fthiella Feb 23 '17 at 14:20
I was doing your split strings to [rows](http://stackoverflow.com/a/17942691/3470178) but fiddle doesnt work :( – Juan Carlos Oropeza Feb 23 '17 at 14:29
@JuanCarlosOropeza sqlfiddle looks down at the moment... anyway I included the fiddle in the answer, second part, so you can try it on your own server if you wish – fthiella Feb 23 '17 at 16:07
Yes, I move it to rextester, but wasnt working for a typo. Is fix now. – Juan Carlos Oropeza Feb 23 '17 at 16:09

score 0 · Answer 3 · answered Apr 06 '21 at 05:30

0

select id,title,upper(left(title,1)) AS FirstLetter from store order by title

answered Apr 06 '21 at 05:30

Ali Umair

690
7
10

MySQL SELECT first letter of the words sequentially for searching

3 Answers3