Got error 'nothing to repeat at offset 1' from regexp

Question

I use php to send and receive data from mysql database my query is

SELECT 
 *
FROM (

  SELECT 
   *
  FROM
   test
  WHERE
   MATCH(word) AGAINST('+hello ')      
) AS fulltext_scan
WHERE 
 fulltext_scan.word REGEXP '^hello '

when I search for english word its working well but when I search for swedish(ä,ö,å) word I got this error

Got error 'nothing to repeat at offset 1' from regexp

I have array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'") in my connection and header('Content-Type: text/html; charset=utf-8');

when I test the query directly from phpmyadmin its working well even with swedish words

I want to use AS for sorting like

SELECT  pages.*, MATCH (head, body) AGAINST ('some words') AS
      AND  column REGEXP '^hello ' relevance,
        MATCH (head) AGAINST ('some words') AS title_relevance
    FROM  pages
    WHERE  MATCH (head, body) AGAINST ('some words')
    ORDER BY  title_relevance DESC, relevance DESC

to get {hello} first

how can I fix that in my php page?

@WiktorStribiżew but the query working on phpmyadmin directly — cordn, Jun 27 '19 at 20:13
Can you provide an example string with the swedish characters? — user3783243, Jun 27 '19 at 20:15
when I search for "hello" the code working in my php page but when I search for "ögon" I just get the error — cordn, Jun 27 '19 at 20:18
Check https://stackoverflow.com/a/2446818/3832970. The error is certainly originating from the fact that the accented letters are turned to bytes and the first byte is a quantifier (I suspect `?`). — Wiktor Stribiżew, Jun 27 '19 at 21:13
[This resource](https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql) might turn out useful. — Wiktor Stribiżew, Jun 27 '19 at 21:24
@WiktorStribiżew - Caution... There are good points about that 'resource'. But, it has several incorrect statements that have not been fixed in years. One has to read all the Comments and hope that they are more correct. — Rick James, Jun 27 '19 at 22:56

score 0 · Answer 1 · answered Jun 27 '19 at 22:38

0

What is the goal? To see if word starts with ögon? That task can very efficiently be done with

WHERE word LIKE 'ögon %';

together with INDEX(word), assuming it is a VARCHAR, not a TEXT.

Please provide SHOW CREATE TABLE test. There could be issues with the CHARACTER SET.

What do you get with SELECT HEX(word) ...? If you are properly using utf8/utf8mb4, it should show C3B6 67 6F 6E (without spacing).

Unless you are using MySQL 8.0, do not expect REGEXP to properly understand accented letters.

There is possibly no need for the extra SELECT; simply do

SELECT ...
    WHERE MATCH(word) AGAINST(...)
      AND word LIKE ...

The MATCH will happen first, then the LIKE will check the few rows that MATCHed.

answered Jun 27 '19 at 22:38

Rick James

135,179
13
127
222

thank you but I want to use ```AS``` for sorting like ```SELECT pages.*, MATCH (head, body) AGAINST ('some words') AS AND column REGEXP '^hello ' relevance, MATCH (head) AGAINST ('some words') AS title_relevance FROM pages WHERE MATCH (head, body) AGAINST ('some words') ORDER BY title_relevance DESC, relevance DESC``` to get {hello} first – cordn Jun 28 '19 at 00:04
`AS AND` does not compute. What is that a typo for? – Rick James Jun 28 '19 at 02:48

Got error 'nothing to repeat at offset 1' from regexp

1 Answers1