Oralce 9i Help With Functions [message #8893] |
Wed, 01 October 2003 13:10 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
Hey Everyone,
I hope you can help me with this. I have a question that I can't seem to answer. I think that you use the concat function to do this.
Do a query using single row functions that flip the name so that it reads first middle(if any) last instead of last, first middle
Doe/John should be flipped to be John Doe
Smith/Mary S should be flipped to be Mary S Smith
Adams-Costa/Susan Ann should be fliiped to be Susan Ann Adams-Costa
Langley/M Richard should be flipped to be M Richard Langley
Here is the table:
SQL> select * from custtable;
IDNO NAME CONTACT AMTDUE ACCTEXP
-------- ------------------------------ --------------- ---------- ----------
DUEDATE AGE
--------- ----------
1111 Doe/John Ann Prescott 600.99 24
08-OCT-83 22
2222 Smith/Mary S Joe Smith 444.56 12
11-NOV-91 33
3333 Adams-Costa/Susan Ann Josh Chace 121.85 15
23-JAN-93 43
Desc of table:
SQL> desc custtable;
Name Null? Type
----------------------------------------- -------- ----------------------------
IDNO VARCHAR2(8)
NAME VARCHAR2(30)
CONTACT CHAR(15)
AMTDUE NUMBER(6,2)
ACCTEXP NUMBER(2)
DUEDATE DATE
AGE NUMBER(5)
|
|
|
Re: Oralce 9i Help With Functions [message #8896 is a reply to message #8893] |
Wed, 01 October 2003 14:02 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
sql>select name,
2 substr(name, instr(name, '/') + 1) || ' ' ||
3 substr(name, 1, instr(name, '/') - 1) flipped_name
4 from t;
NAME FLIPPED_NAME
------------------------------ ------------------------------
Doe/John John Doe
Smith/Mary S Mary S Smith
Adams-Costa/Susan Ann Susan Ann Adams-Costa
Langley/M Richard M Richard Langley
4 rows selected.
|
|
|