Search for comma delimited values

Startbeitrag von Michael Drechsel am 12.06.2014 07:26


I often need to search for comma delimited values in a text field of my database

For example:

I have a field "city" which contains ",Berlin,London,Paris,Rom,"

The user should be able to search for one or many cities.

I divide the search values in pieces like ",London," and ",Rom," and build a query with a "or" statement like

ParaCityPiece1 or ParaCityPiece2 or ParaCityPiece3

It works, but you have to decide how many different Cities at the same time the user can search.

Any other ideas ?



Can you not use the full-text search index of WinDev?

von JP - am 12.06.2014 08:59
not really.

1. I have bad expierience with that. Its slow (if you have a 1 GB index it tooks a few minutes to load it in a memory)
2. it found not all words (for example "80-1234" you can´t search because "-" is not allowed as a part of a word)
3. what is the search string ? "Rom Berlin" or "Berlin Rom" are the same but different strings

von Michael Drechsel - am 12.06.2014 09:09
In WD19 I created a full-text search index on a 2 million record file combining several fields. The ftx is 0.3Gb in size. The fic is 0.9 Gb in size. I find searches instantly. I use the +, -, and quote search prefixes.

"Rom Berlin" would be searched as Rom Berlin without the quotes so that if either word is found the result is true.

You cant use - in the search as that is a search prefix for must-not-contain. That would be a stumbling block indeed. But I find it, on the whole, very powerful and very fast.

von JP - am 12.06.2014 09:21
Hi Michael

because of the limitations of the full text system (and because I coded all that long BEFORE it existed), I'm doing all this by code :
- in the hadd,hmodify, hdelete global procedure overwriting the native functions, I extract all words (or combinations of words, or combination of letters, depending on the application need) from the strings (on one or several or ALL fields of the record, again, depending on the need) and write them each as one record in my dictionary file. *
- I also write the corresponding "fuzzy" value, using a "pseudo-phonetic" function in a secondary key (and of course the filename and unique key)
- when searching, I just need a hreadseek (or query) on that file to find my MAIN record

It is extremely fast for searching, but may tae some time for each hAdd/hModfy, because I now have to write many records...

So, when writing speed is important too, I do the extra process in a secondary thread or exe, by having a flag in the main record. I just set it as NOT treated when adding/modifying (delete HAS to be treated immediately, but is rare, so it's not a problem) and my secondary process search for this flag value and process them (this can be done directly on the server).

Best regards

von Fabrice Harari - am 12.06.2014 10:39
Zur Information: hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.