ABBREVIATION FUNCTION
SQL> CREATE OR REPLACE FUNCTION USER_ABBRIVATION(P_TEXT IN VARCHAR2) RETURN VARCHAR2 IS
2 V_RET_VAL VARCHAR2(2000):=SUBSTR(P_TEXT,1,1);
3 V_EXCEPTION EXCEPTION;
4 V_ERRM VARCHAR2(1000);
5 V_WORD CHAR(1);
6 BEGIN
7 IF P_TEXT IS NULL OR LENGTH(P_TEXT)<=1 THEN
8 V_ERRM:='TEXT VALUE MUST BE MORE THEN ONE CHARACTER, AND CONTAIN SPACES, BLANK VALUES NOT APPLICABLE HERE..';
9 RAISE V_EXCEPTION;
10 ELSE
11 FOR I IN 1..LENGTH(P_TEXT) LOOP
12 V_WORD := substr(P_TEXT,I, 1);
13 IF V_WORD = ' ' THEN
14 V_RET_VAL := V_RET_VAL||substr(P_TEXT,i+1,1);
15 END IF;
16 END LOOP;
17 END IF;
18 RETURN V_RET_VAL;
19 EXCEPTION
20 WHEN V_EXCEPTION THEN
21 RAISE_APPLICATION_ERROR(-20191,V_ERRM);
22 END USER_ABBRIVATION;
23 /
Function created.
SQL> SELECT USER_ABBRIVATION('&ANY_COMPANY') FROM DUAL;
Enter value for any_company: PAKISTAN INTERNATIONAL AIRLINE
USER_ABBRIVATION('PAKISTANINTERNATIONALAIRLINE')
----------------------------------------------------------------------------------------------------
PIA
1 row selected.
SQL> SELECT USER_ABBRIVATION('&ANY_COMPANY') FROM DUAL;
Enter value for any_company: WORLD HEALTH ORGANIZATION
USER_ABBRIVATION('WORLDHEALTHORGANIZATION')
----------------------------------------------------------------------------------------------------
WHO
1 row selected.
No comments:
Post a Comment