Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: Transforming fields

    This is very basic but I need to know how to transform a field in the data source table that contains the first and last name to the new new table that seperates them, ie Table 1 (Field - Name (first and last) and Table 2 (Field - First Name, Surname) what would be the most easiest way to do this>?

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Transforming fields

    It seems a basic question for you, and is source of income for numerios ETL suppliers. The usual problem of "normalizing" your name field into surname and lastname is the algorithm. Has every name exactly 1 surname? What about a middlename? Is your surname abbreviated? Is it always the same order?

    I expect a mix of answers, which makes it very difficult to describe a procedure. I would suggest to add a surname and lastname field to your table holding the name. Apply the transformation as you would expect to fit most of your cases. Check the results manually record by record.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Posts
    6

    thanks for the quick response

    Thanks I figured that would probably be the response. as you are very correct in thinking that the field holds different types and all are very mixed. I managed to create another field and do the process manually, however I only had a ltd number of records : hence it didnt take too long. My question is, what if I had so many records then the time taken would just be too long. any ideas..>?

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: thanks for the quick response

    I would automate as much as possible by applying that algorithm, which is fitting most of your cases. Then, you will have to organize a manual check procedure. Maybe you have to split your table, create several EXCEL sheets, spread them over your organization, and process the corrections.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doktoblue is right that this problem is very common, but is also very difficult and time consuming due to people's "creativity" in entering names. I once worked for a company where a salesperson entered the phrase "rude receptionist" in the name column of their CRM system, unaware that this would be printed on an envelope and mailed to their customer on company letterhead!

    If this will help, here is some Access VB code I wrote years ago to deal with this problem. It returns the Honorific, First name, Middle name, or Last name for most variations.

    You can convert this algorythm to TSQL, or use it an an MS Access utility that you linked to your SQL Server data.

    There are two functions, PARSE_NAME(full_name As String, PART As String) and FIRST_PART, which is called by PARST_NAME.

    I hope this helps you out.

    blindman
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •