Home » SQL & PL/SQL » SQL & PL/SQL » Regarding the SQL (merged 3) (oracle 9i)
Regarding the SQL (merged 3) [message #443596] Tue, 16 February 2010 08:07 Go to next message
kommerashashi
Messages: 23
Registered: November 2007
Junior Member
Dear Friends,

I have One doubt

Example :--- My Name Is "Shashi Kommera"

i need to display this name as "Kommera Shashi"

Is this Possible Without Using ||(canocatenate Operator) or concate Function.

if this query will; get please send me as soon as possibl;e

Re: Regarding the SQl [message #443605 is a reply to message #443596] Tue, 16 February 2010 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 13950
Registered: September 2008
Location: Rainy Manchester
Senior Member
How is the data stored in the db - one column or two?
Re: Regarding the SQl [message #443606 is a reply to message #443596] Tue, 16 February 2010 08:20 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Use SUBSTR and INSTR to find the space, everything before it and everything after it.
Re: sql [message #443610 is a reply to message #443596] Tue, 16 February 2010 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By the pricking of my thumbs a pointless interview question this way comes.

Actually - given that this is almost certainly an interview question, I'll give you a pointer, but not the answer.

Clue: It's one of the REGEXP_.... functions.
Re: sql [message #443612 is a reply to message #443610] Tue, 16 February 2010 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Clue: It's one of the REGEXP_.... functions.

Not in 9i. Wink

So the answer is: not possible before 10g.

Regards
Michel

[Updated on: Tue, 16 February 2010 08:40]

Report message to a moderator

Re: sql [message #443627 is a reply to message #443612] Tue, 16 February 2010 09:41 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Just for fun, maybe so:
WITH DATA AS
 (SELECT 'Shashi Kommera' ID FROM DUAL) 
  SELECT RPAD (RPAD (id2, 8, ' '), 14, id1) di
    FROM (SELECT SUBSTR (ID, 1, 6) id1, SUBSTR (ID, 8, 15) id2 FROM DATA)

di
-----------------
Kommera Shashi
Re: sql [message #443628 is a reply to message #443612] Tue, 16 February 2010 09:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or:
create table test_143(col_1  varchar2(100));
insert into test_143 values ('Shashi Kommera');

select replace(replace(replace(replace(col_1,name_1,'$'),name_2,'%'),'%',name_1),'$',name_2)
from (select col_1
            ,substr(col_1,1,instr(col_1,' ')-1) name_1
            ,substr(col_1,instr(col_1,' ')+1) name_2
      from   test_143);
Re: sql [message #443630 is a reply to message #443628] Tue, 16 February 2010 10:21 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@_jum, @JRowbottom, useless but neat. /forum/fa/2115/0/

Regards
Michel
Previous Topic: Passing IN parameter of datatype varchar2 (merged 5)
Next Topic: Executing script files in a directory
Goto Forum:
  


Current Time: Sun Nov 03 01:53:05 CDT 2024