Using Wildcards for Find/Replace in a field?

[Dr Kyle]

Ok guys... since you came to my aid and solved my other question so quickly yesterday, I am gonna rely on you again...

I have a field in a table which contains several entries of data in each record... Each entry is a series of letters and numbers, following three types of formats (# are digits).

1. FBgn########

2. CG#### (eg. the letters CG followed by 4 digits)

3. CG##### (CG followed by 5 digits)

I need to do a find/replace in this field to find all occurences of a CG with either 4 or 5 digits and remove it (replace with nothing). I have tried using wildcards in the find/replace function, but they dont find any of the CG's.

Any ideas???

[Reed]

you'll have to use find mode to search for records containg those patterns in a field, then use a calculated replace field contents to strip the CG.

[Lee Smith]

If you have access to Developer 7 or Advance 8, you should take a look at the file that is linked to this Thread by [color:red]The Shadow

I have been fooling around with his Custom Function and file and I think you could do what you want with a script.

HTH

Lee

[Dr Kyle]

Reed: I have tried what you suggest, but I cant get it to work. What wildcards to I use to get it to recognize any occurences of CG followed by numbers? I realize I might have to do this replacement twice (once for cases with 4 numbers, once with 5) but that is fine.

I have been using:

Substitute (Field_ID, "CG****", "")

It runs fine, but never finds any of the CG numbers and removes them. Same thing when I use # or @ instead of the *. Any ideas?

Nope, I dont have access to anything but FM8 Pro.

KYLE

[Raybaudi]

Hi

if all the occurrences of CG#### and CG##### have to be setted to "" and no other code contains CG, simple make a find of CG !

[Dr Kyle]

"and no other code contains CG, simple make a find of CG!"

Do you mean that I should do a find/replace using CG! as the find part???

[Raybaudi]

No, I mean:

1)Enter find mode

2)Place CG into the field you want to search

3)Hit the return key

You'll see all the record contains CG into that field

Than, with the cursor inside that field, make a Records-->Replace Field Contents... with "".

Be sure of the found set before doing this !!

[Dr Kyle]

The problem is that there are other entries in many of these records in the same field.

Example: In the same field, I could have...

CG1234

CG12345

FBgn1234567

FBgn8901234

Each entry in the field is followed by a carriage return however, so they can be individually "referrenced" or used to link/relationship.

In the example above, I need a way to remove the CG1234 and CG12345 (and their carriage returns) while leaving the FBgn entries intact. I have ~14,000 records to do this for.

KYLE

[Raybaudi]

Ok, now I understand your problem !

Give me some time to think !

[Dr Kyle]

LOL thanx for trying to help me here... I am pretty new to FM and a lot of things beyond IF calculations and doing lookups using relationships is beyond me right now.

KYLE

[Lee Smith]

I don't think you can do this in FileMaker, at least in the Standard Edition. Perhaps you could do this if you had Developer or Advance as I stated in my earlier post, I tested it with the custom function and a script and was able to remove them.

However, it this is a text file that you are importing, then make the changes the old fashion way, using a text editor such as BBEdit, Textwrangler, etc. and their ability to do grep find and replaces and Regular Expression.

Lee

Edited October 31, 2005 by Guest

[Raybaudi]

Hi

if those CG's are always at the first lines of your field ( so they are showed befor the other codes like your last example), you can try this script:

If[MiddleValues(YourField;1;1)="CG"& GetAsNumber(MiddleValues(YourField;1;1))&"¶"]

Insert Calculated Result[select;YourField;RightValues(YourField;ValueCount(YourField)-1)]

End If

This is only an example, so I haven't make a Loop..

Give me some feedback !

[Dr Kyle]

Unfortunately, the CG's are dispersed among the FBgn's... so they could be at positions 1 thru N in the field.

So your calc wont deal with that right? Sorry that I am so bad with FM guys... I really do appreciate the help here.

I was thinking the suggestion to use a text editor (never heard of BBedit or textwrangler but I get the idea)... but the problem may be compounded by the fact that other fields in each record also contain CG numbers and need to keep them there. Just this field needs to get rid of them.

Probably my last post for the night guys. I will keep playing with it for a few then start fresh in the AM at work.

I will update you tomorrow about it...

KYLE

[Lee Smith]

kyle,

The two programs I mentioned are both for the Mac (Textwrangler is FREE), however there are equivalents for Windows platform also (EditPad is one I think). The point is, you need a text editor that can use Regular Expression Grep Pattern in the find and replace dialog box.

If you attach a copy of your import text file, maybe I can help you figure out the patterns to use, at least it's worth a try.

Lee

[comment]

Hmm... There's a lesson somewhere here about putting needles in a haystack, then looking for them...

As it is, without access to custom functions, you will need to loop a Set Field step, until all occurances of "CG..." are removed. See the attached demo.

KillValues.fp7.zip

[Raybaudi]

Bravo comment !

[Lee Smith]

Very Good comment.

I knew if I said it couldn't be done in FM, either you, Ray, JT, Ugo, etc. would jump at the chance to prove me wrong.

An alternative would be to "NOT" import the data in the first place, by using a Text Editor to clean it up first: The Grep following your sample file would be:

Find:

CGd+r

Replace

with nothing.

Lee

[comment]

I know very little about GREP, but seems to me that wouldn't work when the line starting with "CG" is the last (or only) one, with no r following it.

[Dr Kyle]

Wow, you guys are really coming to my aid here... I appreciate it.

As you suggested, I am attached a comma-seperated txt file containing just two fields... A record number field (so I can reinput the data) and the field which needs the CG's removed from it. If anyone wants to tackle it, I wish you the best and thank you very much.

KYLE

Flybase_Genelistv4.23.txt

[Dr Kyle]

Ok comment, I downloaded your demo and it is beautiful!!! I imported the script to my file, but since I am an idiot and dont know much about scripting yet, I couldnt make all the changes necessary to get it to work with my table.

In my last post I attached a .txt file with the field in question (and a ref. field). This time, I am attaching the whole file (in a zip file) with all records and fields. It is the "secondary_Id" field which needs the CG's removed, while the other fields remain the same.

Sorry again that I am not very good with FM8 here...

Flybase_Genelist.zip

[Lee Smith]

I know very little about GREP, but seems to me that wouldn't work when the line starting with "CG" is the last (or only) one, with no r following it.

I think you are tugging on my leg a little here.

I also think you know that a grep can have more than one pattern in it. So, if what you stated was the case, you would only need to trap for it with "|CGd+ " The Vertical Line, a.k.a the Pipe character, separates different patterns for a grep search, that you want to use the same replace with. So the whole search would look like this:

CGd+r|CGd+

I see that Kyle has also posted his files, so I'll look at them to see if there are any other changes needed.

Lee

[comment]

I think you are tugging on my leg a little here.

Sadly, no. I meant what I said: grep is too geeky for my head. Actually, I am very grateful to you for your example - now I know twice as much as before.

[Lee Smith]

I had to save the file to my hard drive so that I could make the changes, so I'm hoping that I didn't miss anything. Test the import on a test file to see if it works before using a working copy.

I would do this like this

Find:

CGd+r|CGd+|CGd+|"CGd+"r

Replace with nothing

Find

x0Bx0B

Replace

x0B

The x0B is the upside down [color:red]? that represent the returns in your field.

HTH

Lee

[Lee Smith]

Oops forgot the file. Also, the to boxes were invisible in my file so they were pasted in error.

Also, I noticed when zipping the file that there a lot of these

,""

so I modified the

Find to:

CGd+r|CGd+|CGd+x0B|"CGd+"r|,""

Replace with nothing

The second find now is:

Find:

x0Bx0Bx0B|x0Bx0B

Replace

x0B

Let me know how this works for you

Lee

1130778914-Flybase_Genelistv4.23.txt.zip