Search text fields with punctuation characters

[EDC]

Hi,

We have a text field in our database that usually has punctuation characters "," ":" "-" "." and so on. The issue is that sometimes de search in that fiels does not work as expected. To improve the search in some cases when a user try to search in that field we transform the sentence changing the punctuation characters to " ", but sometimes it is not enough, so we have tried to do the same changing the puntuaction character to *, but the result is not good.

For example if the field contain AGENTE X-17, OPERACION OCEANO and we search AGENTE X*17* OPERACION OCEANO we find nothing. Any idea why it is not working properly?

Thanks.

[comment]

The pattern:

X-17

is two words. When you search for:

X*17* 

Filemaker looks for a single word that starts with "X" and contains "17", e.g. "xy217z".  

 

 

[Ocean West]

you will also get different word counts if you have a break character with a number following it

 

WordCount ( "A111-B222-C333-D444" ) = 4

WordCount ( "A111-B222-C333-444D" ) = 3

 

[comment]

1 hour ago, Ocean West said:

if you have a break character with a number following it

It depends not only on the character following the punctuation mark, but also on the character preceding it:

WordCount ( "A-1" ) = 2

WordCount ( "1-1" ) = 1

And these results are not necessarily the same for all punctuation marks.

 

[LaRetta]

Here is my documentation on word break rules, taken mostly from discussions here on FMForum over the years and MOSTLY from Comment  🙂

The ampersand and hyphen are word delimiters - but they are not the only ones. 
There are many more, first and foremost a space, of course. I don't know if there's a comprehensive list, 
but at least !@%^()≠+[]{}<>≤≥`~|\? are all word delimiters.

Word delimiters are not words, so you cannot get them with the MiddleWords() function. 

space
*
#
;

"
=
$
€
&
/
≈
∏
‡


Detailed feedback:
If I go to FileMaker Pro 11 Help for MiddleWords, the example uses spaces and the note refers to ampersand(&) 
and hyphen(-) to identify the beginning of a new word. No other symbols are given. I was curious about what other symbols 
might be able to be used. I created an Excel spreadsheet with the ASCII 255 characters and created text with separators 
using each of the characters. When I imported it into FileMaker Pro, I used MiddleWords in my calculation, 
I found that a period (char 46) did not work, I did find the following symbols did work:
Char 9 is ", "Char 10 is ", Char 11 is , Char 12 is , "
Char 13 is ", Char 32 is , Char 33 is !, Char 34 is ",
Char 35 is #, Char 36 is $, Char 37 is %, Char 38 is &,
Char 40 is (, Char 41 is ), Char 42 is *, Char 43 is +,
Char 44 is ,, Char 45 is -, Char 47 is /, Char 58 is :,
Char 59 is ;, Char 60 is <, Char 61 is =, Char 62 is >,
Char 63 is ?, Char 64 is @, Char 91 is [, Char 92 is \,
Char 93 is ], Char 94 is ^, Char 95 is _, Char 96 is `,
Char 123 is {, Char 124 is |,
Char 125 is }, Char 126 is ~, Char 128 is €, Char 130 is ‚, Char 132 is „, Char 133 is …, Char 134 is †, Char 135 is ‡, Char 136 is ˆ, Char 137 is ‰, Char 139 is ‹, Char 147 is “, Char 148 is ”, Char 149 is •, Char 150 is –, Char 151 is —,
Char 152 is ˜, Char 153 is ™, Char 155 is ›, Char 161 is ¡, Char 162 is ¢, Char 163 is £, Char 164 is ¤, Char 165 is ¥, Char 166 is ¦, Char 167 is §, Char 168 is ¨, Char 169 is ©, Char 171 is «, Char 172 is ¬, Char 173 is ­, Char 174 is ®,
Char 175 is ¯, Char 176 is °, Char 177 is ±, Char 180 is ´, Char 182 is ¶, Char 183 is ·, Char 184 is ¸, Char 187 is », Char 191 is ¿, Char 215 is ×, Char 247 is ÷,

NOTE:  The character ` acts as a word separator on the Macintosh but not in Windows.
RULE 1 (period .)
one.two = 1 word
one.2 = 2 words
1.two = 2 words
1.2 = 1 word

RULE 2 (slash / hyphen - colon : comma ,)
one-two = 2 words
1-two = 2 words
one-2 = 2 words
1-2 = 1 word

RULE 3 (single quote ')
one'two = 1 word
one'2 = 2 words
1'two = 2 words
1'2 = 2 words

I think it is very important information.  The old links are no longer available from what I can tell, which is why I'm just posting my 'combined-gathered' rules here again.

Edited December 24, 2019 by LaRetta

[Ocean West]

Fantastic recap.

[EDC]

Thank you all of you for the info!