Splitting the full name and writing it to another table in SQL Server 2008

I have a table, say A, in which there is a column FULLNAME. Values stored under this column are in the format of “surname name middle_name” (with one space between each). And I have another table B, in which I have columns SURNAME, NAME and MIDDLENAME. What would be the best way to take all of the FULLNAME cells from the table A, split them accordingly and insert them into the table B?

Thanks

  • Filtering results based on most common occurrences of values in other columns (excluding outlying values)
  • SQL count occurrences rows
  • Trigger Error: The current transaction cannot be committed and cannot support operations that write to the log file
  • SQL Server Stored Procedure Update not working with ASP.Net C# GridView RowUpdating
  • Should I use SqlString or string as parameter type to SQLCLR UDF's
  • Why SQL Server doesn't treat this code erratic?
  • Returning multiple values from a stored procedure
  • Using window function quantity until total consumed
  • Is this the right order to clear SqlDataAdapter and Sql Command Parameters and close the connection?
  • How to replace all values in grouped column except first row
  • how to convert date to a format `mm/dd/yyyy`
  • join tables and SUM columns for each distinct row with some rows are considered as column
  • 2 Solutions collect form web for “Splitting the full name and writing it to another table in SQL Server 2008”

    You can combine functions for searching an occurence in a string (which return normally its index) with the Substring function, besides you will need the Left and Right functions

    For example in SQL Server you will find the functions:

    CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

    SUBSTRING ( expression ,start , length )

    LEFT ( character_expression , integer_expression )

    RIGHT ( character_expression , integer_expression )

    STEPS:

    1. Use the LEFT to get the 1st word (integer_expression = index of 1st
      Emtpy space)
    2. Use Substring to get the middle word (start is the index of 1st
      Emtpy space + 1 , length is the entire length – the second index of
      the emtpy space, use the startlocation to search the second occurence which should be the first occurence +1)
    3. Use the right function to get the last word similar to step 1

    Notice that if you have any names including empty spaces in the middle (example a first name like anna maria) this wouldnt work as expected.

    This query will spilt your string.

    select left(FULLNAME,CHARINDEX(' ',FULLNAME)), SUBSTRING(FULLNAME,CHARINDEX(' ',name)+1,len(FULLNAME)) from tableA
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.