Results 1 to 5 of 5

Thread: T-SQL Help

  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: T-SQL Help

    Good Day guys

    I have a Table1 and has one column Firstnames like this:

    FIRSTNAMES
    Abram
    Beauty
    Joel
    Jon Jacob
    Rita
    Maggy Samantha
    Tom
    David Jacob

    I need to select only the 1 name per person, that is I want my query to give me the following results

    Abram
    Beauty
    Joel
    Jon
    Rita
    Maggy
    Tom
    David

    Please help with the script

    I used this

    select left(firstnames, CHARINDEX(' ', firstnames)) from table1
    But it doesn't return to me candidates who has 1 name, please help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    Go
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(FNAME varchar(255))
    GO
    
    INSERT INTO myTable99(FNAME)
    SELECT 'Abram' UNION ALL
    SELECT 'Beauty' UNION ALL
    SELECT 'Joel' UNION ALL
    SELECT 'Jon Jacob' UNION ALL
    SELECT 'Rita' UNION ALL
    SELECT 'Maggy Samantha' UNION ALL
    SELECT 'Tom' UNION ALL
    SELECT 'David Jacob'
    GO
    
    SELECT SUBSTRING(FNAME,1, CASE WHEN CHARINDEX(' ',FNAME)-1 = -1 
    			       THEN LEN(FNAME) 
    			       ELSE CHARINDEX(' ',FNAME)-1 END) AS FNAME 
    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.

  3. #3
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    T-sql

    Thanks Brett, it works like a bom

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select left(firstnames, CHARINDEX(' ', firstnames + ' ')) from table1

    You'll want to wrap the thing in a RTRIM function to drop trailing spaces...
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not sure...but it seems the blind dude's (and yours) should be less expensive...

    Don't know how you'd quanitfy it...expect by looking at it...

    Code:
    SELECT '"' + RTRIM(LEFT(FNAME, CHARINDEX(' ', FNAME + ' '))) + '"' 
      FROM 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
  •