Lin, I use RBase for everything at the office. However, I use Excel to help with viewing data as well, espcecially if it is a one time shot to look at the data. Sometimes manual manipulation is easier than getting the code just right for everything. Since this is going to be cumbersome task at best, export the data to a spreadsheet. There is a conversion process that allows the parsing of the data into separate fields. It is under Data - Text to Columns. You can specify that the key for separation is a space, coma, tab etc. This will place the information into as many columns as you have information to fill.
I realize this may be more cumbersome, but it will allow you to more view the data as you are parsing the information and make changes as you view the data. Once you have completed the task, the reload RBase with the data. You may have to sort a couple of times to separate those with middle names vs those without middle names. You should be able to come up with information in very short order. Just another way of looking at it... Gary ----- Original Message ---- From: Lin MacDonald <[EMAIL PROTECTED]> To: RBASE-L Mailing List <[email protected]> Sent: Monday, January 14, 2008 5:59:53 PM Subject: [RBASE-L] - Re: pulling a text column apart Nice! Lin On Mon, 14 Jan 2008 08:24:57 -0800 (PST), "James Bentley" <[EMAIL PROTECTED]> said: > Lin, > > The key to solving your problem is the following function: > "ITEMCNT > Top Previous Next > > (ITEMCNT('Text String')) > > Use to count the number of items in a text string separated by > current comma delimiter. > > In the following example, the value of vItems is 3. > > SET VAR vItems = (ITEMCNT('a,b,c')) " > > In essence you load the 8,000 items to a temp table with the > following fields: > > 1 original_field > 2 first_name > 3 mid_name > 4 last_name > one field for any other items in in original_field > 5 FieldCnt=(ITEMCNT(original_field)) integer > > Define a command file as follows: > set var voriginal_field TEXT > SET VAR vfieldcnt integer > SET VAR iv1 INTEGER -- set up indicator variables since > original_field could be null > set var ... for other fields > DROP CURSOR C1 > DECLARE C1 CURSOR FOR SELECT original_field, fieldcnt from > temptable > OPEN C1 > FETCH C1 INTO voriginal_field INDICATOR iv1, vFieldcnt iv2 > WHILE SQLCODE = 0 THEN > IF iv1 <> -1 THEN -- we have values in original_field > SWITCH (.vfieldcnt) > CASE 3 > SET VAR vfirstname = (ssub(voriginal_field,?)) > set var other field > UPDATE temptable set firstname=.vfirstname, ... > WHERE CURRENT OF C1 > BREAK > CASE 2 > SET VAR .... > UPDATE > BREAK > DEFAULT > BREAK > ENDSW > FETCH C1 INTO voriginal_field INDICATOR iv1, vFieldcnt iv2 > ENDW > DROP CURSOR C1 > RETURN > You will need to flesh out the above code. It should give you a > good start. For the records without a comma to distinguish > fields you may have to add code to insert commas to separate > fields. > > Jim Bentley > > --- Lin MacDonald <[EMAIL PROTECTED]> wrote: > > > There are about 8000 records in that table, all different, so > > doing it > > by hand is a bit much. They'd have to cart me off to a looney > > bin > > afterwards! > > > > Lin > > > > On Sun, 13 Jan 2008 09:55:07 -0500, "MikeB" > > <[EMAIL PROTECTED]> said: > > > How many rows of data? If it is small, I would just write > > the names to a > > > text > > > file and massage it there to normalize it a bit with > > appropriate > > > delimiters > > > then load as ascii. > > > > > > If there were only a few offenders to a particular > > separation then by all > > > means > > > put it into another table using the string manipulation > > functions and > > > finish > > > the offenders by hand. Thing is, if you spend a great deal > > of time > > > trying to > > > figure out a way to do it programmatically for a one shot > > deal, sometimes > > > you > > > waste time on that instead of just doing the grunt work... > > > > > > > > > > > > > > > ----- Original Message ----- > > > From: "Lin MacDonald" <[EMAIL PROTECTED]> > > > To: "RBASE-L Mailing List" <[email protected]> > > > Sent: Sunday, January 13, 2008 9:36 AM > > > Subject: [RBASE-L] - pulling a text column apart > > > > > > > > > > Sorry, another Newbie question -- > > > > As I redesign my Database and application, I want to > > correct some > > > > design problems. One of them is that in my Supplier > > table, I > > > > have one column for Contacts. I'd like to move that > > column to > > > > another table with multiple fields, such as First Name, > > Last > > > > Name, etc. The problem is that the data in that field is > > not > > > > consistant. It could contain the following: > > > > Null > > > > FName > > > > FName LName > > > > FName1, FName2 > > > > FName1, FName2, FName3 > > > > So, what would be the best way to break it apart? Should > > I use a > > > > temporary table? I thought of using SSUB, but that > > wouldn't work > > > > for the ones that had a First and Last name in the field > > with > > > > just a space in between. > > > > thanks, > > > > Lin MacDonald > > > > > > > > > > > > > > > > > > > Jim Bentley > American Celiac Society > [EMAIL PROTECTED] > tel: 1-504-737-3293 > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > >
