1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Microsoft SQL statement

Discussion in 'Scripting & Programming' started by jamosb23, Mar 6, 2006.

  1. jamosb23

    jamosb23 New Member

    3
    0
    1
    Please help.

    Im trying to merge two colums together and the put the result in a new column and within the merge trim one column down to remove the middle name:

    Current Columns:
    Surname
    blake
    Forenames
    james david

    New Column to read:
    User_ID
    james blake

    what is the statement to trim the column and then merge the two columns together to get the desired result of one column with full name.

    Any help would be appreciated...
     
  2. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    The problem you face is parsing the Forenames field. Not everyone will have 2 forenames, and unless you have a structure to this field (poor normalization) then extracting the correct bits may be a problem.

    I don't know MSSQL, but standard SQL would use substring (string from pattern for escape) and string concatenation.

    And the fact that you want the *first* forename may not make everyone happy. For the first 18 years of my life I was known by my second forename, and the family continues to use it!

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  3. jamosb23

    jamosb23 New Member

    3
    0
    1
    Thats a good help thanks, but im taking this task on for reference purposes on another table. Do you know the scripting that is needed?
     
  4. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    Er - what scripting? You asked for a statement, so I assumed you were attempting to do this in one update line.

    If you are going to write stored procs then obviously you can handle name variations much better, but it could get complex!

    You say
    . What do you mean by that? I admit that I'm a tad confused on what you are trying to do now!

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+
  5. jamosb23

    jamosb23 New Member

    3
    0
    1
    Sorry to confuse i meant to say statement not script.

    Things have changed now as i have a new table from an active directory with three columns User ID, Surname, Forename.

    So what i need to do is populate the user_id column on employees table with the user_id data from the Active directory table by matching the surname columns from the two tables and using the LIKE term for the fornames column as the AD table only has 1 first name in the column where as the employees table has first and middle in the fornames column.

    Can you help?

    regards
     
  6. hbroomhall

    hbroomhall Petabyte Poster Gold Member

    6,623
    115
    224
    I'd suggest you start with a join on the two tables to see if the results are what you expect. If these two tables have been populated at different times in different ways then you may be shocked at the differences between them!

    When the join is working correctly then you should be able to do an update. MSSQL seems to have an extension to SQL99 in the shape of 'FROM' which will probably simplify the statement, but as I don't have MSSQL I don't know exactly how it works.

    Harry.
     
    Certifications: ECDL A+ Network+ i-Net+
    WIP: Server+

Share This Page

Loading...