OraFAQ Forum: SQL & PL/SQL » SUBSTR Program

Home » SQL & PL/SQL » SQL & PL/SQL » SUBSTR Program

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SUBSTR Program [message #395971]

Fri, 03 April 2009 06:34

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.
....
.

Report message to a moderator

Re: SUBSTR Program [message #395974 is a reply to message #395971]

Fri, 03 April 2009 07:00

Frank
Messages: 7901
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

Report message to a moderator

Re: SUBSTR Program [message #395977 is a reply to message #395974]

Fri, 03 April 2009 07:07

Frank
Messages: 7901
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

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

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.

Report message to a moderator