Working with Strings - MySQL - page 171

To find out what those verses are, select the columns you want to see (the example here
truncates the vtext column and uses \G so the results better fit the page):
mysql> SELECT bname, cnum, vnum, LEFT(vtext,65) AS vtext
-> FROM kjv WHERE MATCH(vtext) AGAINST('Hadoram')\G
*************************** 1. row ***************************
bname: Genesis
cnum: 10
vnum: 27
vtext: And Hadoram, and Uzal, and Diklah,
*************************** 2. row ***************************
bname: 1 Chronicles
cnum: 1
vnum: 21
vtext: Hadoram also, and Uzal, and Diklah,
*************************** 3. row ***************************
bname: 1 Chronicles
cnum: 18
vnum: 10
vtext: He sent Hadoram his son to king David, to inquire of his welfare,
*************************** 4. row ***************************
bname: 2 Chronicles
cnum: 10
vnum: 18
vtext: Then king Rehoboam sent Hadoram that was over the tribute; and th
The results may come out in book, chapter, and verse number order, but that's just
coincidence. By default, full-text searches compute a relevance ranking and use it for
sorting. To make sure a search result is sorted the way you want, add an explicit OR
DER BY clause:
SELECT bname , cnum , vnum , vtext
FROM kjv WHERE MATCH ( vtext ) AGAINST ( ' search string ' )
ORDER BY bnum , cnum , vnum ;
To see the relevance ranking, repeat the MATCH() … AGAINST() expression in the output
column list.
To narrow the search further, include additional criteria. The following queries perform
progressively more specific searches to determine how often the name Abraham occurs
in the entire KJV, the New Testament, the Book of Hebrews, and Chapter 11 of Hebrews:
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham');
+----------+
| COUNT(*) |
+----------+
| 229 |
+----------+
mysql> SELECT COUNT(*) from kjv
-> WHERE MATCH(vtext) AGAINST('Abraham')
-> AND bsect = 'N';