CODEWITHSUNDEEP
sqlsql-serverdatabasesplit
Javid Bunyadzade
Javid Bunyadzade

Reputation: 145

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

Upvotes: 1

Views: 1836

Answers (2)

CloudyMarble
CloudyMarble

Reputation: 37566

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.

Upvotes: 4

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

This query will spilt your string.

select left(FULLNAME,CHARINDEX(' ',FULLNAME)), SUBSTRING(FULLNAME,CHARINDEX(' ',name)+1,len(FULLNAME)) from tableA

Upvotes: 0

Related Questions