SQL search text

Classic Servoy
1

Hi
I use Mysql and I need to make search for any field.
For example, I have the name Juan Antonio Martín
If I seach Juan or Antonio or Juan Martín is ok but if I search Martín Juan is not found
I don´t want use Full-Text in Mysql because for that I need change the database format InnoDb to MyISAM and I don´t want

How can I do that?

Is more better use PostGreSql?

Thanks

2

Hi Juan,

With PostgreSQL you would also have to use full text search (Tsearch). I think a more portable way is to parse the criteria yourself and adjust the query to find all instances.
So if you want to search for Martín Juan you cut the string up per word and make the SQL that searches for Martín OR Juan.
So your code would look something like this:

var _sSearchString  = "Martín Juan",
    _aSearchString  = _sSearchString.split(" "),
    _sQuery         = "SELECT id FROM myTable WHERE myColumn LIKE '%" + _aSearchString.join("%' OR myColumn LIKE '%") + "%'";

controller.loadRecords(_sQuery);

Or to make it case insensitive:

var _sSearchString  = "Martín Juan",
    _aSearchString  = _sSearchString.split(" "),
    _sQuery         = "SELECT id FROM myTable WHERE UPPER(myColumn) LIKE UPPER('%" + _aSearchString.join("%') OR UPPER(myColumn) LIKE UPPER('%") + "%')";

controller.loadRecords(_sQuery);

Hope this helps.