SQL Server Performance

One field into three

Discussion in 'Getting Started' started by batchahaja, May 16, 2007.

  1. batchahaja New Member

    Hi. I have name field as person_name in my database as one field. I want to produce a report in the formate of first_name,last_name and middle_name. How can splet the one name field into three.

    For example i have the data as
    Haja Moinudeen Mohammed
    what i need is
    First_name :Haja
    Middle_name :Moinudeen
    Last_name :Mohammed
    If i use left of and right of functions it will fetch fixed no of characters it may leads to wrong result. Plz help me for my solution.

    Thanks..
  2. satya Moderator

    Its a presentation layer and you can do with
    Select FirstName + " " + LastName
    and look into Concatenation topic in Books online.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. FrankKalis Moderator

    Note that you might need to takt NULLs into account and thus might be better off, wrapping the columns in ISNULL().

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. FrankKalis Moderator

    Haha, got fooled by satya's reply! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />You're right using LEFT() and RIGHT(). The length of each substring that you need to extract can be determined by CHARINDEX and (CHARINDEX() in combination with REVERSE().<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. satya Moderator

    Doh, I had thought in concatenation way as mixed up with another question. [:I]http://www.databasejournal.com/features/mssql/article.php/3071531 fyi
    Ignore my reply....

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. Madhivanan Moderator

    I agree with presentation part [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />It can be done easily there with the help of split function [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page