All excellent advice. One more thing which is my pet peeve. Take this opportunity to set the case on your data if it's like mine used to be: all lower case or all caps with an occasional correct mixed case thrown in. I now control case at the data entry point.
Claudine :) -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Sunday, January 13, 2008 10:20 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: pulling a text column apart 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 > >
