If you have got some consistency with most records falling into a few fairly
well defined groups then you could add another column to the table and flag
each one into a group.
Say you have a group for first and last name with no delimiter, another
where there is a delimiter, a third where you really don't know quite how to
handle it, that sort of thing, then you can manipulate the groups separately
to get them into the format that you want.
You can possibly automate some of the grouping process by putting those with
a comma in the record into one group (update xxx set group = 1 where [col]
contains ',') then further refine manually.
From experience, I don't think that you'll find an easy way but you might
easily find ways to convert the bulk and only have to take time over the
remainder.
One trick I have used before is to put a pipe symbol (|) where I think a
break might be and split on the pipe before tidying up. For instance, you
might use SRPL to replace all spaces or commas or whatever seems appropriate
with a pipe then split into columns. If you sort each column in turn you
might find it easy to group certain names. If you're careful, you can change
the R:base delimiter from a comma to a pipe and use SSUB but don't forget to
change it back again!
Copy your table to another name and work on that so that if you mess up you
can easily go back to the original. If your original doesn't have an id
column give it one and ensure that you keep that the same in your copy
table. You'll then be able to update one table from the other easily.
One other thing, try and decide what you really want to end up with before
you begin - so many times you find that the goalposts move as you work when
you come across things that don't fit your original plan. My names table has
a column for first name another for main name and a computed full name. Any
middle names or initials go in the first name since they're relatively rare
and I don't need to search or sort on them. If you think you might need that
sort of distinction get it in at the start - it'll be easier to move that
data elsewhere later and delete the column than to do further splitting.
Good luck,
Regards,
Alastair.
----- Original Message -----
From: "Lin MacDonald" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Sunday, January 13, 2008 3:15 PM
Subject: [RBASE-L] - Re: pulling a text column apart
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
>
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.516 / Virus Database: 269.19.2/1221 - Release Date:
12/01/2008 14:04
