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
