MySQL Forums
Forum List  »  Full-Text Search

Re: how to find particular string from text column
Posted by: Rick James
Date: May 25, 2010 06:21PM

How many different "columns" will you be searching on? If not too many, you should put the values into their own columns (possibly redundantly with the key-value TEXT string you already have).

LIKE 'Selling Agency:Yes'
RLIKE '(^|\|)Selling Agency\s*:\s*Yes(\||$)'

The LIKE version assumes:
* Selling Agency:Yes is not a substring anywhere else.
* The spacing is consistent. Eg, no spacing diffs: "Selling Agency :Yes"

The RLIKE version:
* Is slower
* Makes sure Selling Agency:Yes is exactly bounded by either '|' or start/end of string.
* The spacing must be consistent. Eg, no spacing diffs: "Selling Agency :Yes"
* Allows optional spacing on either side of the ':'.

FULLTEXT is not applicable for this task.

"like is not giving me exact distinct count" -- find a row that is violating your query. (SELECT text ... WHERE text NOT LIKE '...'). It should be obvious what is wrong (such as extra spacing).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to find particular string from text column
4530
May 25, 2010 06:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.