Can I substitute with wildcards?

[Chalkster]

I am importing data from another program into a data base

The basic data is the same, but it is contained in long strings which I have to break down into various fields and tables and reformat

Most of the work is done but I am struggling with one issue

I have one set of data strings which use one of two separators between values. All the data in this string is going into one field in one record II have cut it out from the long string and am down to the last step.

The rules for the string are

values are 3 characters separated by either a space or a "/". Each value is either a three character uppercase alphabetical value, or a "/" followed by two uppercase alphabetical characters

The problem is that "/" can also be the initial character of a value as well as a separator

So

ABC DEF /GH /JK is a possible string

ABC/DEF /GH//JK is also possible

There is no question of validating data here... it is all preformatted and was validated before the other program exported it. I am simply trying to differentiate between the "/" when used as a separator and when used as an initial character. The ones used as a separataor I need to change into a "-" but the ones used as initial character of a three character group remain as they are.

Is there anyway of writing a substitute function that can say understand a wildcard meaning any alphabetical letter? I thought Substitue might be best because there is no theoretical limit on number of three character values possible in a single field , though in practice I would not expect to encounter more than one hundred at maximum.

I can change the double "//" occurences easily enough and I can also change the ones where the "/" follows the space as first character of a group. My problem is when the "/" is used as a separator amongt alphabetical characters without one of these two identifiers

So that I can say something like /*** to mean "/" followed by 3 alphabetical chatacters and just substitute something in place of the"/" leaving the characters as they are

So

/JKL might become -JKL but /CA would remain as /CA

Hope this makes sense?

[comment]

There is no wildcard in Filemaker's text functions. However, this should work:

Substitute ( String ;

[ " /" ; "-#" ] ;

[ "//" ; "-#" ] ;

[ "/" ; "-" ] ;

[ " " ; "-" ] ;

[ "#" ; "/" ] )

[LaRetta]

Note that Comment is using # as a replacement character and then converting that character at the end back into /.

This shouldn't be confused with # for finding a number of digits, such as:

#3 finds 53 and 43 but not 3

If someone is familiar with using # in the find process, this might be confusing. I mention it because of the subject. Wildcards CAN be used in finds but not functions (as Comment mentions).

LaRetta

[Chalkster]

Thanks guys (and that isnt meant as a sexist remark LaRetta:)

One question then ... in a Substitution does FMP apply one at a time... ie substitution 1 then substitution 2 .... etc in order so one character may be changed more than once .... rather than substitutions at the same time?

I never thought of it in this way... I had considered doing something similar as a script as a set field series of steps, but if the Substitute function works in order that is perfect ... thanks!

I knew about wildcards in Finds but wasnt sure if they applied in other functions

[LaRetta]

I like being called a guy.

Well, at least being grouped with them.

[Chalkster]

one small problem I had to change

Comments calculation missed one possibility. When the first character in the string was "/" it changed it to a "-" thus a valid value of /CA became -CA if it was the first group, when it should have remained as the valid value of /CA

But you gave me enough to work it out, I used a Case statement to check this and fixed it... so thanks again for pointing me in the right direction

[comment]

Ah. The first character. You could use:

Right (

Substitute ( " " & String ;

[ " /" ; "-#" ] ;

[ "//" ; "-#" ] ;

[ "/" ; "-" ] ;

[ " " ; "-" ] ;

[ "#" ; "/" ] ) ;

Length ( String ) )

Incidentally, whoever came up with the idea of using the same character as a separator AND as valid data is a very smart person...