Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Splitting a combined field

    In the past I made a query that took employee names (each sperate field) combined them so they displayed Last, First Middle in the query view. I haven't done any work in months, so I am slipping.

    Thanks
    Ryan
    My Blog

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there a question lurking in there somewhere?

    -PatP

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    haha Pat. Basically how do I do it.
    Ryan
    My Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rguy84
    haha Pat. Basically how do I do it.
    Usually you take her out a couple of times, then...

    Wait a second... Do what?

    Seriously, I don't understand what you're asking me how to do, so I don't have a clue how to help you do it. Keep in mind that I'm an old phart, you need to kind of point me in the right direction before I get a good grip on things.

    -PatP

  5. #5
    Join Date
    Jul 2006
    Posts
    11
    The question IS highly ambiguous? Do you want to split a name field that stores first middle and last names, or do you have three fields and you want to combine them?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But you do have a backup of that ancient query ... somewhere ... don't you? Obviously not

    As I understand it, you want something like this (based on Oracle):
    Code:
    SQL> CREATE TABLE NAMES
      2  (first    VARCHAR2(20),
      3   middle   VARCHAR2(20),
      4   last     VARCHAR2(20)
      5  );
    
    Table created.
    
    SQL>
    SQL> INSERT INTO NAMES (first, middle, last) VALUES ('Guy', 'R', 'Eightyfour');
    
    1 row created.
    
    SQL>
    SQL> SELECT * FROM NAMES;
    
    FIRST                MIDDLE               LAST
    -------------------- -------------------- --------------------
    Guy                  R                    Eightyfour
    
    SQL>
    SQL> SELECT last ||', '|| first ||' '|| middle ||'.'
      2  FROM NAMES;
    
    LAST||','||FIRST||''||MIDDLE||'.'
    ----------------------------------------------------------------
    Eightyfour, Guy R.
    
    SQL>
    Basically, you'll need a concatenation operator - || in Oracle, might be something different in your database.

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Okay I have a query that shoots out
    PatPhelan
    I wan't it to add a space
    Ryan
    My Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select left('PatPhelan',3) + ' ' + right('PatPhelan',6) as newcolumn
    and you will, of course, need to know where to insert the space on every name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Yes, can't you do like
    Code:
    SELECT tblPeople.FirstName and tblPeople.LastName AS firstlast
    FROM tblPeople
    (SELECT left([firstlast], tbl.People[FirstName])+ ' ' 
    + right([firstlast], tbl.People[LastName]);
    Ryan
    My Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you mind just quickly explaining again what it is you're actually trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From your combined posts, I think I can figure out what you're trying to get to. My best guess is something like:
    Code:
    SELECT
       first_name + ' ' + last_name -- 'John Doe'
    ,  last_name + ', ' + first_name + ' ' initial + '.' -- 'Doe, John A.'
       FROM tblPeople
    -PatP

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Rudy, I want to make a query that takes two fields and slaps them together into one field in a temp table with a bit of formatting
    Ryan
    My Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, ryan, for the very flexible requirements

    Code:
    select slap_together(one_field,two_field) as new_field
         , format(bit_field,'X') as blue_field
      into temp_table
      from your_table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    the slap_together() concerns me...
    Ryan
    My Blog

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arrrr, as it should, matey!! as it should!!

    avast, did ye look it up in yer foine manual, then?



    sorry, just practicing ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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