Introduction: Hi guys, i am going to demonstrate how to split string in sql server. While working with an application i faced a situation where i have a table in which i have to import data from several other tables. The structures of those tables were different(diff columns names, nos and datatypes). Although i was happy because i have imported data from all those tables. but i got a situation where i have some columns with null values and i had to fill that based on data imported.
Description: I had a user table in which i had to fill records from Customer, Supplier, Employees etc tables. In customer and supplier table we had a column named Fullname and in destination table(User) i had first name and last name column. I had created a temporary column in my destination table to import all those records . And i had to fill that first name and last name based on data from fullname field. After a lot of trials i was able to achieve my goal. I am going to post that sql script i have used for this purpose.
UPDATE DBO.USERS SET FIRSTNAME = LEFT(FULLNAME, CHARINDEX(‘ ‘, REVERSE(FULLNAME)) – 1), FULLNAME=RIGHT(FULLNAME, CHARINDEX(‘ ‘, REVERSE(FULLNAME)) – 1).