在SELECT语句中向单词的左侧和右侧添加空格

Question

I have a working SELECT statement. However, I'm wondering if instead of the word 'one', is it possible to use the word ' one ', instead. With spaces to the left and right of 'one' so that it makes sure that it's not part of another word. So, for example, if the word is something like 'someONE', it wouldn't get selected. Please take a look at my code.

SELECT input, (CHAR_LENGTH(input) - CHAR_LENGTH(replace(input, 'one', '')))/CHAR_LENGTH('one') AS matches
FROM allData
HAVING matches >= CEIL(CHAR_LENGTH(input)/5)
AND matches = ( 
SELECT MAX( (
(CHAR_LENGTH(input) - CHAR_LENGTH(replace(input, 'one', '')))/CHAR_LENGTH('one')
) ) 
FROM allData ) 

I tried using ' one ', instead of 'one', but it returned no results when I did that, when previously, using just 'one', it returned 2 rows of results. Here's a sqlFiddle for anyone who want to test the actual code out for themselves:

http://sqlfiddle.com/#!9/136e3/2

Answer 1

You might try this :

SELECT *
FROM allData
WHERE (input like '% one %'
       OR input like '% one'
       OR input like 'one %'
       OR input like 'one')