There is a large table with street names. I need to choose from it only those that contain a hyphen and name, for example "Tverskaya-Yamskaya 1st".

I make a query: SELECT * FROM tbl_name WHERE street REGEXP '/^[А-Яа-я]+\-[А-Яа-я]+\s[0-9]+[-я]+/ui'

The result should be something like this table:

 +------+---------------------+ | id | street | +------+---------------------+ | 5 | Тверская-Ямская 1-я | | 6 | Тверская-Ямская 2-Я | | 7 | Тверская-Ямская 3-Я | | 4 | Тверская-Ямская 4-Я | +------+---------------------+

But in my case, the result is empty.

I tried to do the same in php:

preg_match('/^[А-Яа-я]+\-[А-Яа-я]+\s[0-9]+[-яЯ]+/ui', 'Тверская-Ямская 1-я', $matches);

And I see the result:

 Array ( [0] => Тверская-Ямская 1-я )

Tell me, please, what is my mistake?

Thank!

    2 answers 2

    Try changing the selection condition as follows:

     WHERE street REGEXP '[а-я]+-[а-я]+ [0-9]+[-я]'

    Regular expressions in MySQL

      Not valid characters for sql regular expressions, such as "/ / ui", "\ s", are not working for you. You could do without regular expressions, for example, with the operator LIKE:

       SELECT * FROM tbl_name WHERE street "%-% %-%"