SQL Server Performance

split name

Discussion in 'General Developer Questions' started by Reddy, Feb 22, 2006.

  1. Reddy New Member

    I have a name field which has data like 'Richard, Gayle'. I want to splt this name into fields like lastName and firstName and insert into 2 different fields. How can query that?

    "He laughs best who laughs last"

  2. Madhivanan Moderator

    You can very well handle this in your Front End application if any


    Declare @str varchar(100)
    set @str='Richard, Gayle'
    Select @str as Full_Name,
    Substring(@str,1,charindex(', ',@str,1)-1) as First_name,
    Substring(@str,charindex(', ',@str,1)+2,len(@str)) as Last_name


    Failing to plan is Planning to fail
  3. Reddy New Member

    Actually the thing is I have to delete the NAME field and add 2 new columns with LASTNAME and FIRST NAME. So I need to get data into new fields from the old NAME field, and thought of doing this from database, i dont need that from front end app.

    "He laughs best who laughs last"

  4. Reddy New Member

    I need a query so that i can update all my rows in a table not just a single row in this manner.

    "He laughs best who laughs last"

  5. Madhivanan Moderator

    Then why do you want to delete that column?
    Use the logic I used so that you dont need to seperate columns


    Failing to plan is Planning to fail
  6. Reddy New Member

    The thing is i want to change my design in that way like deleting the old column NAME and adding 2 new columns LAST and FIRST. once i move the data to new columns then want to delete tha old column

    "He laughs best who laughs last"

  7. Madhivanan Moderator

    Create two columns. Then run this

    Update yourTable
    Set FirstName=Substring(NameCol,1,charindex(', ',NameCol,1)-1),
    SecondName=Substring(NameCol,charindex(', ',NameCol,1)+2,len(NameCol))

    Then run select

    Select * from yourTable

    If you are satisfied with the result drop Namecol


    Failing to plan is Planning to fail
  8. Reddy New Member

    I am getting the following error when i run that query, what does it mean.

    Server: Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the substring function.
    The statement has been terminated.

    "He laughs best who laughs last"

  9. Madhivanan Moderator

    Some of your Names dont have comma. Post such data here


    Failing to plan is Planning to fail
  10. FrankKalis Moderator

  11. Reddy New Member

    yeah u r right, i observed such data in it.

    Bailey, Amy (Ann)
    Mattucci, Emile J.
    Cressinger. Lisa

    "He laughs best who laughs last"

  12. Reddy New Member

    YEah Frank my column do allow nulls

    "He laughs best who laughs last"

  13. Madhivanan Moderator

    So Cressinger. Lisa need to splitted as Cressinger and Lisa


    Update yourTable
    Set FirstName=Substring(replace(NameCol,'.',','),1,charindex(', ',replace(NameCol,'.',','),,1)-1),
    SecondName=Substring(replace(NameCol,'.',','),,charindex(', ',replace(NameCol,'.',','),,1)+2,len(NameCol))


    Failing to plan is Planning to fail
  14. Reddy New Member

    still i get an error like

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ','.

    "He laughs best who laughs last"

  15. FrankKalis Moderator

    If you can't improve the quality of your raw data, I would probably consider doing this in a scripting language where you can use regular expressions. Your T-SQL code might become really ugly.

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
  16. Reddy New Member

    My data is unique now except few rows where it has "." instead of ",".
    I am just getting the syantax error now

    "He laughs best who laughs last"

  17. Madhivanan Moderator

    Try this

    Update yourTable
    Set FirstName=Substring(replace(NameCol,'.',','),1,charindex(', ',replace(NameCol,'.',','),1)-1),
    SecondName=Substring(replace(NameCol,'.',','),charindex(', ',replace(NameCol,'.',','),1)+2,len(NameCol))


    Failing to plan is Planning to fail
  18. mccabe New Member

    try adding

    WHERE NameCol LIKE '%,%'

    to the updates, then it will only update rows that are correct.
    Then you can check remainging rows with

    SELECT *
    FROM ...
    WHERE FirstName IS NULL /* OR perhapt = '' */

    if its a few, just update those rows manually.
    If its many, either apply some logic or just set 'SecondName' to the entire contents and leave 'FristName' blank...


  19. Mostafa New Member

    Dear All,I want any one support on the following case :i have a colomn that contains arabic names (the name starts from the right) and the seperator between the names are (Space) and i want to split this name into first, second, third, fourth and fifth name and meanehile some of these names have no spaces so how can i exclude them or even deal with them with any way .Any one feedback will be highly appreciated .thanks .
  20. FrankKalis Moderator

    Please start a new thread with sample data and required output. This will improve your chances getting good replies in a timely manner.

Share This Page