Finding / Escaping characters

[Wickerman]

I'm trying to clean up some field data where some users of a database enclosed titles in a TITLE field with "Quotation Marks" while other ones did not.  I want to remove the quotation marks from those who did include them.

 

I figured I would create a new field called TITLE_New and use some sort of calculation strategy to find the relevant title records and massage the data into it while leaving the original Field data alone.  Once I'm satisfied the data in the New field looks good, I'll delete the old field and use the new one in its place.

 

As a warm-up I thought I'd search for the records where the first character was a quotation mark (").  I thought I recalled that I'd have to use the symbol to "escape" that character so the calc engine wouldn't read it as a quotation make and demand a second one someplace.   So I scripted a Quickfind on:

 

Left(Inventory::Title; 1) = """

 

When that didn't work I thought I'd just make sure I was performing  a find properly and tried the simple:

 

Left(Inventory::Title; 1) = "A"

 

And when that didn't work properly I realized that, jeez, I don't even know how to find records where the first character of a field is 'A'.

 

Clearly I'm rusty on this, as I've done all sorts of selective character hunting in the past, but I'm stumped.  Can someone give me a pointer -- on performing Finds for particular starting (or ending) characters, particularly 'special' ones like ".  

 

thanks!

Of course if there's a simple way to address my original goal of removing the quotation marks, that would be great too!

[rivet]

Try http://www.filemaker.com/13help/en/html/func_ref3.33.88.html#1031764

[LaRetta]

I'm not sure why your finds did not work.  To manually find a single quote, type:  "  

 

If that does not work then you have smart quotes so you will need to copy one and use it in your find.  A script to clean it up might look like this, using Rivet's suggestion:

 

BACK UP FIRST.  :-)

Enter Find Mode [ ]
Set Field [ table::field ; """ ] 
Set Error Capture [ On ]
Perform Find [ ]
If [ Get ( FoundCount ) ]
Replace Field Contents [ table::field ; Substitute ( table::field ; """ ; "" ) ] [ No dialog ]
End If

Edited March 1, 2014 by LaRetta

[comment]

Do you want to remove ALL quotation marks in the field - or are there some exceptions?

 

 

So I scripted a Quickfind on:

 

Left(Inventory::Title; 1) = """

 

You cannot use calculations as search criteria.

 

 

 

  A script to clean it up might look like this, using Rivet's suggestion  ...

 

A script like that requires a backup before anything else.

[LaRetta]

Added.  Thank you!

[Wickerman]

Thanks all -- I think this gives me plenty to go on . .  .

 

But one thing I don't understand, LaRetta, is this entry for the Find Set Field:

 

Set Field [ table::field ; """ ]

 

 

Why the 3   ?   Why not just """

 

Also -- the quotation marks aren't standalone -- they're immediately followed by (or following) a character, like:

 

"The Grapes of Wrath"

 

. . . that's why I was thinking I'd need to go with something like Left(Title;1) -- to isolate that first character and see if it's a " mark.   

 

Comment -- I don't want to remove quotation marks throughout the Title -- just ones at the beginning or end.

[LaRetta]

The find criteria which ends up in the field must be " to search for "

 

But the needs to be escaped as well so """

 

... the first escapes the second and the third escapes the second quote.

 

Where else would you have quotes except around a quote or a title?  And if the quotes were around a quote someone said how would the computer know it is not a title?  

In the Titles, is every word within Title case?

[Lee Smith]

Also, be sure you are not working with smart quotes a.k.a curly quotes instead of plain quotes.

[comment]

Comment -- I don't want to remove quotation marks throughout the Title -- just ones at the beginning or end.

 

Aha!

 

I would suggest you define a new calculation field (result is Text) =

Substitute ( "§" & Title & "§" ; [ "§"" ; "" ] ;  [ ""§" ; "" ] ; [ "§" ; "" ] )

Observe the results and report back.

Edited March 1, 2014 by comment

[comment]

As an aside:

 

To find records where the field begins with a quote, enter this into the field as the search criteria:

=="*

To find records where the field ends with a quote, use:

==*"

[Wickerman]

Ah -- great, for what I'm needing to do today, the ability to simply find the records with the quote at beginning or end turns out to be nearly all I need.   It had never occurred to me to use a wildcard in concert with the 'entire field' match!

 

Q: Is there a similarly simple search criteria I can use to find records with a Quote anywhere in the middle of the text string?

 

Comment -- I also tried the the Substitute calc and it didn't seem to have any effect I could see.  I'm unfamiliar with the "§" symbol -- what does it mean?

 

LaRetta -- thanks for the explanation.  My mind is reeeellling.

 

Albert

[comment]

Q: Is there a similarly simple search criteria I can use to find records with a Quote anywhere in the middle of the text string?

 

For this, enter:

"

as the search criteria. Note that this refers to manual search. If you want to use Set Field[] to enter the criteria in a script, you need to escape the literal text as shown earlier by LaRetta.

 

 

I'm unfamiliar with the "§" symbol -- what does it mean?

 

it doesn't mean anything. I use it as "a string I presume will not appear anywhere in the text". If you are dealing with historical legal documents, use something else instead. Like 淾, for example*.

 

 

Comment -- I also tried the the Substitute calc and it didn't seem to have any effect I could see.

 

Did you check you don't have smart (curly) quotes in your field? Go to a record that starts with a quote, open Data Viewer and look at the result of:

Code ( Left ( Title ; 1 ) )

If it's not 34, then it's not a quotation mark .

 

 

---

(*) Have no idea what that means. Hopefully it's not something rude  - and if it is, hopefully it's juicy.