в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:58:52Z

1 версия

Новая страница

==LPAD() and RPAD(): pad the input parameter of character data types with blanks (or another character) from the left or right==



<source lang="sql">

Syntax:LPAD(<character string>, <length_of_resulting_string> [,<padding_string>])
RPAD(<character string>, <length_of_resulting_string> [,<padding_string>])

SQL> SELECT
2 LPAD("ABC",6,"***") result
3 FROM dual;
RESULT
------
***ABC
SQL>

</source>

==Padding department with < and >==



<source lang="sql">

SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK
3 primary key
4 , dname VARCHAR2(10)
5 , location VARCHAR2(8)
6 , mgr NUMBER(4)
7 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING", "DALLAS", 4);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 9);
1 row created.
SQL>
SQL> select dname
2 , lpad(dname,9,">")
3 , rpad(dname,6,"<")
4 from departments;
DNAME LPAD(DNAM RPAD(D
---------- --------- ------
ACCOUNTING ACCOUNTIN ACCOUN
TRAINING >TRAINING TRAINI
SALES >>>>SALES SALES<
HR >>>>>>>HR HR<<<<
SQL>
SQL>
SQL> drop table departments;
Table dropped.

</source>

==RPAD() function"s behavior is identical to that of LPAD(), the only difference being the padding side.==



<source lang="sql">

SQL>
SQL> SELECT
2 RPAD("ABC",6,"***") result1,
3 LPAD("ABC",4,"***") result2,
4 RPAD("ABC",2,"***") result3
5 FROM dual;
RESULT RESU RE
------ ---- --
ABC*** *ABC AB

</source>

==Rpad: makes a string a certain length by adding (padding) a specified set of characters to the right==



<source lang="sql">

SQL> -- Rpad: makes a string a certain length by adding (padding) a specified set of characters to the right .
SQL>
SQL> -- RPAD(string, length_to_make_string, what_to_add_to_right_of_string)
SQL>
SQL> SELECT RPAD("Letters", 20, ".") FROM dual;
RPAD("LETTERS",20,".
--------------------
Letters.............

</source>

==select rpad( "*", 5, "*" )==



<source lang="sql">

SQL>
SQL>
SQL> select rpad( "*", 5, "*" )
2 from dual
3 /
RPAD(
-----
*****
1 row selected.
SQL>
SQL> --

</source>

==Use rpad to add extra space==



<source lang="sql">

SQL>
SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select rpad( ename, 10, " " ) || "(" || job || ")" "Responsibilities"
2 from emp
3 order by ename
4 /
Responsibilities
---------------------
ADAMS (CLERK)
ALLEN (SALESMAN)
BLAKE (MANAGER)
CLARK (MANAGER)
FORD (ANALYST)
JAMES (CLERK)
JONES (MANAGER)
KING (PRESIDENT)
MARTIN (SALESMAN)
MILLER (CLERK)
SCOTT (ANALYST)
SMITH (CLERK)
TURNER (SALESMAN)
WARD (SALESMAN)
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL> --

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Char Functions/RPAD - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия