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?
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 )
- Use the LEFT to get the 1st word (integer_expression = index of 1st
- 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)
- 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