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) 登录后复制 |
mysql> select * from test;+-------------------+| name|+-------------------+| word|| word ok || ok word || wordword|| wordword wordword || (word)(word)|| ,word,|+-------------------+7 rows in set (0.00 sec)
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) 登录后复制 |
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)
The correct method is to use the REGEXP and the [[:<: and>:]] word-boundary markers:
mysql> select * from test where name regexp '[[:<:]]word[[:>:]]';+--------------+| name |+--------------+| word || word ok|| ok word|| (word)(word) || ,word, |+--------------+5 rows in set (0.01 sec) 登录后复制 |
mysql> select * from test where name regexp '[[:<:]]word[[:>:]]';+--------------+| name |+--------------+| word || word ok|| ok word|| (word)(word) || ,word, |+--------------+5 rows in set (0.01 sec)
–EOF–
GD Star Rating
loading...
GD Star Rating
loading...
全网最新最细最实用WPS零基础入门到精通全套教程！带你真正掌握WPS办公！ 内含Excel基础操作、函数设计、数据透视表等
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
578
收藏
