1 2 Previous Next 22 Replies Latest reply on Sep 19, 2011 4:54 AM by RGoyette

    Searching a text string



      Searching a text string


      This is related to my unicode question, which did not produce much traffic.


      Lets say I have thirty records, each containing a text field named TITLE.  Sometimes TITLE contains a unicode character which is entered in the form, for example, like " −".  TITLE can contain more than one unicode character.  I can use Char(8722) to display the unicode character.  So what I need to do is search TITLE for all the places that &# occurs, and for each occurance get the contents between the &# and the ; (the numbers inbetween the two) and then replace the #&8722; with Char(8722).  I need to perform this search and replace on the contents of TITLE for each of the thirty records.  Can anyone suggest an elegent solution?

        • 1. Re: Searching a text string

          Substitute ( Title ; " &#8722" ; Char ( 8722) )

          A replace fields step with the calculation option can make this change on all the records in your found set in one go (say just after importing the data from another source...)

          • 2. Re: Searching a text string

            Somwthing like that was my plan, but it was getting to idenifying how many substitutions were necessary and what was being substituted that I was problematic.  More specifically, not every entry in TITLE will contain the same unicode character so I have to search each entry in TITLE to see which contain unicode and if so how many and which ones and then do the replace.

            • 3. Re: Searching a text string

              A single substitute function can substitute for multiple text patterns all in one go. Since I don't know other unicodes off hand, I'll make one up just for the purposes of showing the syntax (which you can also look up in FileMaker help...)

              Substitute ( Title ; [" &#8722" ; " " & Char ( 8722)] ; [" &#9722" ; " " & Char ( 9722)] )

              Just add as many substitution pairs in brackets as you need. (I've also included the leading space character here to preserve correct spacing--something I missed the first time around.)

              Note that if a given unicode does not exist in Title, then no change is made so you don't actually have to search any of your fields to see if any given unicode pattern exists--you can just use this method once for a "batch fix" of the problem.

              Also, if your users are entering these codes directly into a FileMaker database, you can use this expression in an auto-enter calculation to update the Title field the instant they exit the field.

              • 4. Re: Searching a text string

                You could try this:

                Substitute ( Title ; [ "&#" ; "Char(" ] ; [ ";" ; ")" ] )

                ... you might get bit if there are other semi-colons in the field not associated with the ending code.

                • 5. Re: Searching a text string

                  I like that one! Should have noticed the ; at the end which makes that simplified code possible here!

                  • 6. Re: Searching a text string

                    It looked promising, but when I tried that the Char(8722) was not interpreted.  Is chould have showed up as a "-" but instead it showed up as Char(8722).  Note that Substitute ( Title ; "&#8722" ; Char (8722) ) did interpret correctly.

                    • 7. Re: Searching a text string

                      Yes, you'd need to put it inside an Evaluate function call.

                      Evaluate ( Quote ( Substitute ( Title ; [ "&#" ; "\" & Char(" ] ; [ ";" ; ") & \"" ] ) ) )

                      • 8. Re: Searching a text string

                        Why the addition of the "\"? 

                        • 9. Re: Searching a text string

                          Using that method I get a title, in the middle of which is a " & Char(8722) & ", so still not interpreting correctly.

                          • 10. Re: Searching a text string

                            \" inserts quotation marks into the text so that & Char ( 8722 ) & is evaluated as part of the expression instead of as part of the literal, quoted text.

                            Interestingly, the Quote function doesn't work here like I would have expected.

                            This expression does:

                            Evaluate ( "\"" &  Substitute ( Title ; [ "&#" ; "\" & Char(" ] ; [ ";" ; ") & \"" ] ) & "\""  )

                            Note though that I think you'll find there's a fatal flaw (as LaRetta mentioned earlier), here in that any ; used as simple punctuation will break this expression. I think a list of substitution expressions in [], while more cumbersome to set up, will work reliably where this one may fail on you.

                            • 11. Re: Searching a text string

                              I think you are correct.  I think what I will do is utilize you rinitial suggestion:


                              Substitute ( Title ; [" &#8722" ; " " & Char ( 8722)] ; [" &#9722" ; " " & Char ( 9722)] )


                              and just keep adding unicode characters to the list as they present themselves.  Thank you both for your help.

                              • 12. Re: Searching a text string

                                Well, one way to tell if you have any records with a lonely semi-colon is with:

                                PatternCount ( string ; ";"  ) - PatternCount ( string ; "&#" )

                                Any records which produce any number should be checked.  We can also test that &# always be characters 5 and 6 prior to semi-colon if needed.  Is this a one-time conversion or an on-going process?

                                • 13. Re: Searching a text string

                                  In theory I will import records from an oracle database and search for unicode once the import is complete.  It is a very small subset of users who manually type the decimal unicode value into their TITLE, so looking for particular codes is workable; but my preference was for a more global solution.

                                  • 14. Re: Searching a text string

                                    Surely you can find a conversion table and import it as a related table.  How many codes are you working with or do you need to allow for them all?  I would never create a Substitution() calculation for this (hard-coding the values).  I would use another table or I would use the calc I provided and search for any lone semi-colons (using second calc I provided) and manually change from ) back to ;.

                                    1 2 Previous Next