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
>
>
