Splitting a combined field

rguy84 · #1 (**permalink**) 08-04-06, 13:20

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

Pat Phelan · #2 (**permalink**) 08-04-06, 14:19

Is there a question lurking in there somewhere?

-PatP

rguy84 · #3 (**permalink**) 08-04-06, 15:00

haha Pat. Basically how do I do it.

Pat Phelan · #4 (**permalink**) 08-04-06, 17:58

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

Jugular Bean · #5 (**permalink**) 08-06-06, 08:09

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?

Littlefoot · #6 (**permalink**) 08-07-06, 01:23

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.

rguy84 · #7 (**permalink**) 08-09-06, 18:37

Okay I have a query that shoots out
PatPhelan
I wan't it to add a space

r937 · #8 (**permalink**) 08-09-06, 18:38

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

rguy84 · #9 (**permalink**) 08-10-06, 12:19

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]);

r937 · #10 (**permalink**) 08-10-06, 12:41

would you mind just quickly explaining again what it is you're actually trying to do?

Pat Phelan · #11 (**permalink**) 08-10-06, 13:15

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

rguy84 · #12 (**permalink**) 09-01-06, 15:34

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

r937 · #13 (**permalink**) 09-01-06, 15:58

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

rguy84 · #14 (**permalink**) 09-06-06, 17:08

the slap_together() concerns me...

r937 · #15 (**permalink**) 09-06-06, 17:23

arrrr, as it should, matey!! as it should!!

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

sorry, just practicing ...

Internet Services

Web Development

Digital Marketing

Consumer Tech