BRAKING A COLUMN INTO TWO

Question

BRAKING A COLUMN INTO TWO

moiduddin

SSC Enthusiast

Points: 159
More actions
February 26, 2008 at 2:59 pm

#186936

Hi,
I need to break a single colomn into two in sql server. Just like name
eg
judith ,kaur
The result should be
First Name Last name
judith kaur
Any suggestions appreciated.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DAVNovak Hall of Fame Points: 3120 More actions · Answer 1

Check out the CHARINDEX function and look for either the space or the comma or both depending on how clean you data is.

You can use the CHARINDEX function in the SUBSTRING function to parse out the first and last names into seperate columns.

Dave Novak

moiduddin SSC Enthusiast Points: 159 More actions · Answer 2

What if there are some hundreds of names and we need to break the column into two.

Can we do it for the whole column rather then taking each and everrow one after the other.

DAVNovak Hall of Fame Points: 3120 More actions · Answer 3

Yes, you can apply it to the entire column. Here is an example of an update sql that take a full name and parse it into the first and last name each in their respective column:

Fullname: 'Smith, Jane'

update employees

set FirstName = SUBSTRING(FullName, CHARINDEX(',', Fullname, 1) + 2, LEN(Fullname) - CHARINDEX(',', Fullname, 1) - 1),

LastName = SUBSTRING(Fullname, 1, CHARINDEX(',', Fullname, 1) - 1)

Whenever you are parsing text, you will find that SUBSTRING, CHARINDEX, PATINDEX, LEFT, and RIGHT are very helpful. I would highly suggest checking these functions out in BOL and learning them. You will use them quite often.

Dave Novak