Chalkster Posted July 10, 2005 Share Posted July 10, 2005 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? Link to comment Share on other sites More sharing options...
comment Posted July 11, 2005 Share Posted July 11, 2005 There is no wildcard in Filemaker's text functions. However, this should work: Substitute ( String ; [ " /" ; "-#" ] ; [ "//" ; "-#" ] ; [ "/" ; "-" ] ; [ " " ; "-" ] ; [ "#" ; "/" ] ) Link to comment Share on other sites More sharing options...
LaRetta Posted July 11, 2005 Share Posted July 11, 2005 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 Link to comment Share on other sites More sharing options...
Chalkster Posted July 11, 2005 Author Share Posted July 11, 2005 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 Link to comment Share on other sites More sharing options...
LaRetta Posted July 11, 2005 Share Posted July 11, 2005 I like being called a guy. Well, at least being grouped with them. Link to comment Share on other sites More sharing options...
Chalkster Posted July 11, 2005 Author Share Posted July 11, 2005 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 Link to comment Share on other sites More sharing options...
comment Posted July 11, 2005 Share Posted July 11, 2005 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... Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6992 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now