Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: changing/reordering text in a field

    I have a table with approx 1200 names in the format of "John Doe" I need to edit the names to the format of "Doe, John"

    Any easy way in SQL to do this or am I better off dumping to text file, making the changes in another app and then updating the table?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I guess in some time you'll need another format - "FN Doe LN John" . It is better to keep first and last name in different fields.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your lucky day. See attached file with function for parsing names.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, Separate columns would be better.

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(myName varchar(50))
    GO
    
    INSERT INTO myTable99(myName)
    SELECT 'John Doe' UNION ALL
    SELECT 'John Apple' UNION ALL
    SELECT 'John Q. Adams' UNION ALL
    SELECT 'Mr. John Doe' UNION ALL
    SELECT 'John Doe III'
    GO
    
    SELECT    SUBSTRING(myName,(CHARINDEX(' ',myName)+1),(LEN(myName)-(CHARINDEX(' ',myName)))) + ', ' 
    	+ SUBSTRING(myName,1,(CHARINDEX(' ',myName)-1))
      FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •