0

I have:

 ----------------------------
| ID          |  Workout     |
|_____________|______________|
| 1           |Chest workout |
| 2           |beach Workout |
| 3           |End workout   |
_____________________________

Basically I want to search a string in my workout column.

Now if you try SELECT * FROM db.table WHERE MATCH (workout) AGAINST('workout'); Or SELECT * FROM db.table WHERE MATCH (workout) AGAINST('Chest'); They Both works fine.

But If you try SELECT * FROM db.table WHERE MATCH (workout) AGAINST('est');

FYI: (est as in Chest) does not works even I have a row named Chest in my workout column.

So I would really appreciate if you tell how to select a string within a full text and within word and order them by characters first character like 1. end workout, 2. beach workout, 3. Chest workout in SQL.

Jay wardan
  • 131
  • 12

2 Answers2

3

Wouldn't a LIKE statement do more of what you are looking for? Something like this:

SELECT *
FROM table WHERE workout LIKE '%est%'

The query above should return anything that contains est.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Joshua S
  • 46
  • 2
  • That pretty much worked and can you tell me how to order them by characters first in word i mean like `SELECT * FROM table WHERE workout LIKE '%e%'` so it should order them like: 1. end workout, 2. beach workout, 3. Chest workout. – Jay wardan Jan 31 '17 at 17:52
  • You should be able to use 'ORDER BY workout desc' or 'asc' depending on what direction you want to go. – Joshua S Jan 31 '17 at 17:55
  • I'm afraid I can't use `like`, as Gordon Linoff said it doesn't use indexes but my table rows are huge so I need an indexed way or a better way to find the string quicker. Do you know any better way from performance perspective? – Jay wardan Jan 31 '17 at 17:58
  • @Jaywardan I believe that you can tune the performance of the like statement. [link]http://stackoverflow.com/questions/1388059/sql-server-index-columns-used-in-like has an excellent breakdown on how to go about it. – Joshua S Jan 31 '17 at 18:08
0

The answer to your question is that you seem to want:

where Workout like '%est%'

'est' is a substring, not a word. That is a very important distinction when using full text indexes. If you need to search for any string of characters, then like has the functionality. MATCH is basically looking for words.

The unfortunate thing is that like won't use indexes in this case. If you have a large amount of data, you might want to look for alternative technologies better suited to this problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786