Post reply

BRAKING A COLUMN INTO TWO

  • 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

  • February 26, 2008 at 3:25 pm

    #782275

    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

  • March 26, 2008 at 10:09 am

    #794180

    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.

  • March 26, 2008 at 1:22 pm

    #794313

    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

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

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