Home » SQL & PL/SQL » SQL & PL/SQL » Reverse Word (Any Oracle)
Reverse Word [message #346172] Sat, 06 September 2008 23:43 Go to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
Word: "WELCOMETOPAKISTAN"

i need this word in reverse order or it can be written in reverse order like that:
"NATSIKAPOTEMOCLEW"

PLS SEND ME THE query;

regards
Shahzad
Re: Reverse Word [message #346174 is a reply to message #346172] Sun, 07 September 2008 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select reverse('WELCOMETOPAKISTAN') from dual;
REVERSE('WELCOMET
-----------------
NATSIKAPOTEMOCLEW

1 row selected.

Regards
Michel
Re: Reverse Word [message #346179 is a reply to message #346174] Sun, 07 September 2008 01:19 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
Dear Sir,
if i not used reverse key word.then how will be query?.can we use procedure by using substr. advised me query.?
regards

Re: Reverse Word [message #346182 is a reply to message #346179] Sun, 07 September 2008 02:02 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
yes you can use length and substr in pl/sql block...
Re: Reverse Word [message #346183 is a reply to message #346182] Sun, 07 September 2008 02:07 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
sir pls advise/send me the query.
Re: Reverse Word [message #346185 is a reply to message #346183] Sun, 07 September 2008 02:44 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
have you tried to search on the functions I gave you.....sad to know that you haven't...don't expect spoon feeding...
Re: Reverse Word [message #346186 is a reply to message #346185] Sun, 07 September 2008 02:49 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
declare
a number;
begin
select length(a1) into a from dual;
select substr(:t,a,-a) from dual;
end;

this is whta i have used.pls advised and made correction in that.
Re: Reverse Word [message #346189 is a reply to message #346186] Sun, 07 September 2008 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide:
- don't use IM speak
- format your post
- copy and paste your session
- at least post something that is syntaxically correct:
SQL> declare
  2  a number;
  3  begin
  4  select length(a1) into a from dual;
  5  select substr(:t,a,-a) from dual;
  6  end;
  7  /
SP2-0552: Bind variable "T" not declared.

We don't spoonfeed the lazy.

Regards
Michel
Re: Reverse Word [message #346196 is a reply to message #346172] Sun, 07 September 2008 04:15 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
i am using this procedure in report. and :T is the column formula.it will gives the following result.
Ans: E
only 1 characteris showing.
Re: Reverse Word [message #346197 is a reply to message #346196] Sun, 07 September 2008 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the only error, syntaxically and logically.

Regards
Michel

[Updated on: Sun, 07 September 2008 04:23]

Report message to a moderator

Re: Reverse Word [message #346198 is a reply to message #346174] Sun, 07 September 2008 04:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Sun, 07 September 2008 10:25

We don't spoonfeed the lazy.
Michel Cadot wrote on Sun, 07 September 2008 07:15
SQL> select reverse('WELCOMETOPAKISTAN') from dual;
REVERSE('WELCOMET
-----------------
NATSIKAPOTEMOCLEW

1 row selected.

Regards
Michel


Say what?
Re: Reverse Word [message #346199 is a reply to message #346198] Sun, 07 September 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I knew this was not the expected answer. Twisted Evil

Regards
Michel
Re: Reverse Word [message #346215 is a reply to message #346199] Sun, 07 September 2008 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REVERSE does the job; however, as it is the undocumented function, it has been said that such solutions should not be suggested to Oracle newbies as some of them might not exactly understand what "undocumented" means.

In a few words, it means that this function is not supported in written documentation and there's no guarantee that this function will exist in any of the future Oracle versions. Therefore, if you rely your code on such a function, you might find yourself in trouble in (near?) future. Also, Oracle will not be responsible for your problems as you chose to use such an undocumented feature.
Re: Reverse Word [message #346217 is a reply to message #346215] Sun, 07 September 2008 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually, the answer to OP's question is: "it is not possible" (see version).

Regards
Michel
Re: Reverse Word [message #346320 is a reply to message #346186] Mon, 08 September 2008 03:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a good piece fo advice, Oracle is not SQL Server.

In oracle, you don't need to use a Select to populate a variable.

Instead of
select length(a1) into a from dual
you should write
 a := length(a1);
It will execute much faster.
Re: Reverse Word [message #346327 is a reply to message #346320] Mon, 08 September 2008 04:09 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
thx sir,
but i need the exact query/procedure. so how i will find that words.because i have change many way but could not find success.
Re: Reverse Word [message #346339 is a reply to message #346172] Mon, 08 September 2008 04:44 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I guess this helps You.
SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure mine_reverse(a in varchar2) as
  2  another varchar2(20);
  3  begin
  4  for d in 1..length(a) loop
  5  another:=another||substr(a,-d,1);
  6  end loop;
  7  dbms_output.put_line('here is reverse  '||another);
  8* end;
SQL> /

Procedure created.


SQL> execute mine_reverse('Pointers');
here is  sretnioP

PL/SQL procedure successfully completed.



Regards,
Pointers.
Re: Reverse Word [message #346343 is a reply to message #346339] Mon, 08 September 2008 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pointers wrote on Mon, 08 September 2008 11:44
I guess this helps You.
  7  dbms_output.put_line('here is reverse  '||another);
  8* end;
SQL> /

Procedure created.


SQL> execute mine_reverse('Pointers');
here is  sretnioP

PL/SQL procedure successfully completed.



Output faked. (Missing "reverse")
Re: Reverse Word [message #346346 is a reply to message #346343] Mon, 08 September 2008 05:26 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Frank wrote on Mon, 08 September 2008 05:15
pointers wrote on Mon, 08 September 2008 11:44
I guess this helps You.
  7  dbms_output.put_line('here is reverse  '||another);
  8* end;
SQL> /

Procedure created.


SQL> execute mine_reverse('Pointers');
here is  sretnioP

PL/SQL procedure successfully completed.



Output faked. (Missing "reverse")




Now check this..

SQL> set serveroutput on;
SQL> execute mine_reverse('pointers');
here is reverse  sretniop

PL/SQL procedure successfully completed.



Re: Reverse Word [message #346353 is a reply to message #346339] Mon, 08 September 2008 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@pointers

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Re: Reverse Word [message #346382 is a reply to message #346353] Mon, 08 September 2008 07:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
See reply #1
Pot and kettle..
Re: Reverse Word [message #346388 is a reply to message #346382] Mon, 08 September 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See answer to your reply #1.
Idem.

Regards
Michel
Re: Reverse Word [message #346702 is a reply to message #346172] Tue, 09 September 2008 08:06 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
not sure, but try using string_agg (by tom?) and hierarchy query.
Re: Reverse Word [message #346708 is a reply to message #346702] Tue, 09 September 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this can be done with stragg as well as with sys_connect_by_path but I think OP question is a PL/SQL exercise.

Regards
Michel
Re: Reverse Word [message #346718 is a reply to message #346708] Tue, 09 September 2008 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
you could go for the 'over the top' approach:
with src as (select 'Pointers' word from dual)
select regexp_replace(xmlagg(letter),'<[^>]+>',null,1,0)
from (
select xmlelement("X",substr(word,level,1)) letter from src connect by level <= length(word) order by level desc
);


Can anyone do it using the Model clause?
Re: Reverse Word [message #346732 is a reply to message #346718] Tue, 09 September 2008 09:42 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I would sign for a movement to create a "askSirJRowbottom.com"!
Re: Reverse Word [message #346740 is a reply to message #346718] Tue, 09 September 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not with model but good old connect by:
SQL> with
  2    data as ( select 'Pointers' word from dual ),
  3    letters as (
  4      select substr(word,length(word)-level+1,1) letter, level rank, length(word) last
  5      from data
  6      connect by level <= length(word)
  7    )
  8  select replace(sys_connect_by_path(letter,'/'),'/','') "Reverse"
  9  from letters
 10  where level = last
 11  connect by prior rank = rank - 1
 12  start with rank = 1
 13  /
Reverse
-----------------------------------------------------------------
sretnioP

1 row selected.

By the way, why "Pointers"?

Regards
Michel

[Updated on: Tue, 09 September 2008 10:09]

Report message to a moderator

Re: Reverse Word [message #346743 is a reply to message #346740] Tue, 09 September 2008 10:23 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Very cool! two websites now =) would wait for the third sql solution! this reminds me of the "palindrome" times...
Re: Reverse Word [message #346745 is a reply to message #346718] Tue, 09 September 2008 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And with model:
SQL> with
  2    data as ( select 'Pointers' word, rownum rn from dual )
  3  select r "Reverse"
  4  from data
  5  model
  6    dimension by (rn)
  7    measures (word, cast(null as varchar2(100)) r)
  8    rules
  9    iterate (100) until (substr(word[1],iteration_number+1,1) is null)
 10    ( r[1] = substr(word[1],iteration_number+1,1) || r[1] )
 11  /
Reverse
-------------------------------------------------------------------------
sretnioP

1 row selected.

Regards
Michel

[Updated on: Tue, 09 September 2008 10:29]

Report message to a moderator

Re: Reverse Word [message #346749 is a reply to message #346745] Tue, 09 September 2008 10:50 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I got a nosebleed on that, will read this first =)
Re: Reverse Word [message #346838 is a reply to message #346215] Tue, 09 September 2008 17:50 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Well, knock me over with a feather. I had no idea REVERSE wasn't documented. We have several functional indexes in our app that use reverse. Now we have to decide if we want to do something else with them.
Re: Reverse Word [message #346869 is a reply to message #346838] Tue, 09 September 2008 23:39 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Machel,

Can You gov me the link where i can information about " connect by " and "sys_connect_by_path".
Re: Reverse Word [message #346890 is a reply to message #346869] Wed, 10 September 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference

Regards
Michel
Re: Reverse Word [message #347411 is a reply to message #346732] Thu, 11 September 2008 09:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thanks for the compliment, but there are several regular posters here who know significantly more than I do.
Re: Reverse Word [message #347448 is a reply to message #347411] Thu, 11 September 2008 12:23 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
JRowbottom
There are several regular posters here who know significantly more than I do.

Regular posters? Several? Significantly? I don't think so.

However, I've seen several irregulars who are capable of producing extraordinary solutions. Too bad they don't spend more time here, but - perhaps most of our problems are just too simple for them.
Previous Topic: Select Count Within Cursor Loop
Next Topic: Retrieving Data Based on MAX()
Goto Forum:
  


Current Time: Fri Dec 09 17:23:31 CST 2016

Total time taken to generate the page: 0.13401 seconds