Rumah > pangkalan data > tutorial mysql > SQL statement to search for Word Boundary_MySQL

SQL statement to search for Word Boundary_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-05-31 08:48:01
asal
1100 orang telah melayarinya
Facebook Twitter Google+ SQL statement to search for Word Boundary_MySQL Pretty Print

Send this article by email

What is your name?

Please indicate below the emails to which you want to send this article: SQL statement to search for Word Boundary

Enter one email per line. No more than 5 emails.

In SQL, sometimes, you want to search a string field that contains a key, but as a whole word. So for example, if you search for ‘ word ‘, you do not want ‘ wordword ‘ to be included in the result. Suppose, we have the following data in the MySQL table  test.

mysql> select * from test;+-------------------+| name|+-------------------+| word|| word ok || ok word || wordword|| wordword wordword || (word)(word)|| ,word,|+-------------------+7 rows in set (0.00 sec)
Salin selepas log masuk
Salin selepas log masuk
mysql> select * from test;+-------------------+| name|+-------------------+| word|| word ok || ok word || wordword|| wordword wordword || (word)(word)|| ,word,|+-------------------+7 rows in set (0.00 sec)
Salin selepas log masuk
Salin selepas log masuk

So, if we use percentage symbol % to match one or a few characters, we can use like “%word%” to match, but this gives unnecessary matches.

mysql> select * from test where name like "%word%";+-------------------+| name|+-------------------+| word|| word ok || ok word || wordword| ---------------> incorrect match| wordword wordword | ---------------> incorrect match| (word)(word)|| ,word,|+-------------------+7 rows in set (0.00 sec)
Salin selepas log masuk
Salin selepas log masuk
mysql> select * from test where name like "%word%";+-------------------+| name|+-------------------+| word|| word ok || ok word || wordword| ---------------> incorrect match| wordword wordword | ---------------> incorrect match| (word)(word)|| ,word,|+-------------------+7 rows in set (0.00 sec)
Salin selepas log masuk
Salin selepas log masuk

The correct method is to use the REGEXP and the [[:<: and>:]] word-boundary markers:

mysql> select * from test where name regexp '[[:<:>:]]';+--------------+| name |+--------------+| word || word ok|| ok word|| (word)(word) || ,word, |+--------------+5 rows in set (0.01 sec)</:>
Salin selepas log masuk
Salin selepas log masuk
mysql> select * from test where name regexp '[[:<:>:]]';+--------------+| name |+--------------+| word || word ok|| ok word|| (word)(word) || ,word, |+--------------+5 rows in set (0.01 sec)</:>
Salin selepas log masuk
Salin selepas log masuk

–EOF–

GD Star Rating

loading...

GD Star Rating

loading...

Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan