Home » SQL & PL/SQL » SQL & PL/SQL » SUBSTR Program
SUBSTR Program [message #395971] Fri, 03 April 2009 06:34 Go to next message
MR[T]
Messages: 62
Registered: March 2009
Member
Hello

i am making a program ,descreption of program is given below ;

INPUT COMPANY NAME.
CONVERT INTO ABBRIVATED FORM


INPUT --------------------------------------------- OUTPUT
PAKISTAN INTERNATIONAL AIRLINE -->> P.I.A.
UNITED KINGDOM -->> U.K.





DECLARE
 INPUT VARCHAR2(30) :='&ENTER_cOMPANY_NAME' ;
 STR1 VARCHAR2(100);

BEGIN
STR1 :=CONCAT(SUBSTR(INPUT,1,1) , '.') || CONCAT(SUBSTR(INPUT, INSTR(INPUT,' ',1),2) , '.') ;
&D(STR1);

END ;
/


Quote:
Output :
Enter value for enter_company_name: UNITED KINGDOM
U. K.

Enter value for enter_company_name: ABC INSTITUTE OF INFORMATION TECHNOLOGY
A. I.


Second time program is only giving me first two characters .
I can increase it by using more substr commands but it will make query too long .
I also used LOOPING FOR it ,but it wasent giving me required opuput


DECLARE
 INPUT VARCHAR2(30) :='&ENTER_cOMPANY_NAME' ;
 STR1 VARCHAR2(100);
 CNTR NUMBER :=0 ;
BEGIN
WHILE CNTR < LENGTH (INPUT) LOOP
STR1 :=CONCAT(SUBSTR(INPUT,1,1) , '.') || CONCAT(SUBSTR(INPUT, INSTR(INPUT,' ',1),2) , '.') ;
&D(STR1);
END LOOP ;
END ;
/
 


OUTPUT

Quote:
Enter value for enter_company_name: NEW ERA
N. E.
N. E.
N. E.
N. E.
N. E.
....
.

Re: SUBSTR Program [message #395974 is a reply to message #395971] Fri, 03 April 2009 07:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
An alternative would be to convert the string to Initcaps. That way the first letter of each word will be uppercase, the rest will be lowercase.
Next replace the spaces with dots and add a trailing dot
Finally remove all lowercase chars
Re: SUBSTR Program [message #395977 is a reply to message #395974] Fri, 03 April 2009 07:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you need an example:
SQL> declare
  2    the_string varchar2(100) := '&enter_your_string';
  3  begin
  4    dbms_output.put_line(replace(translate(replace(initcap(the_string), ' ', '.')||'.', 'abcdefghijklmnopqrstuvwxyz','                         '), ' ', ''));
  5  end;
  6  /
Enter value for enter_your_string: Oracle Rich Aid For All Queries
old   2:   the_string varchar2(100) := '&enter_your_string';
new   2:   the_string varchar2(100) := 'Oracle Rich Aid For All Queries';
O.R.A.F.A.Q.


Regexp_replace would probably even be easier..

[Updated on: Fri, 03 April 2009 07:08]

Report message to a moderator

Re: SUBSTR Program [message #395978 is a reply to message #395971] Fri, 03 April 2009 07:09 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
create table t ( nme varchar2( 255 ) );

insert into t values ( 'PAKISTAN INTERNATIONAL AIRLINE ' );
insert into t values ( 'UNITED KINGDOM'  );
insert into t values ( 'HYPER TEXT TRANSFER PROTOCOL'   );


commit

column initials format a10

select
  rtrim( regexp_replace( nme, '([^ ])[^ ]* *', '\1.' ), '.' ) initials
from t ;

INITIALS
----------
P.I.A.
U.K.
H.T.T.P.



Thanks

[Updated on: Fri, 03 April 2009 07:11]

Report message to a moderator

Re: SUBSTR Program [message #395980 is a reply to message #395978] Fri, 03 April 2009 07:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can make your loop code work (and should, as you need the coding practice).

What you have forgotten to do is to use the loop counter CNTR to specify which word it is that you want to take the first character from, and so you are always taking the first chr of the first two words.
Re: SUBSTR Program [message #396030 is a reply to message #395971] Fri, 03 April 2009 09:57 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
Thanks aLot Frank
user2004 And JRowbottom




@ FRANK

YOUR program is great .., but My teacher havent told me about traslate() funtion Sad
I need to create by using looping


Re: SUBSTR Program [message #396046 is a reply to message #396030] Fri, 03 April 2009 11:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Even if you didn't have to, JRowbottom is right. It is a good exercise to complete the code using loops
Re: SUBSTR Program [message #396048 is a reply to message #395971] Fri, 03 April 2009 11:18 Go to previous messageGo to next message
MR[T]
Messages: 62
Registered: March 2009
Member
I am unable to understand how to use loop here .,please write a qurty that it would help me alot Smile
Re: SUBSTR Program [message #396049 is a reply to message #395971] Fri, 03 April 2009 11:20 Go to previous message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com has many fine coding examples
Previous Topic: Prime Number Code
Next Topic: Procedure Creation : Hanging in Parse phase (merged)
Goto Forum:
  


Current Time: Mon Dec 05 08:56:48 CST 2016

Total time taken to generate the page: 0.05270 seconds