포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ LPAD Oracle Function ]
LPAD함수는 지정된 총 자릿수 n에 대하여 expr1을 먼저 작성하고, 왼편의 남은 공간에 expr2를 채운다. 이 함수는 쿼리의 출력 서식에 대하여 유용하다.
- 함수 설명 : LPAD 오라클 함수 링크
1. Oracle(오라클)
Lpad() 함수
| Oracle Programming |
SELECT LPAD('statwith',15,'*') "LPAD example"
FROM DUAL;
| Results |
LPAD example
----------------
*******statwith
2. Python Pandas(파이썬)
zfill() 함수
- : 숫자 0으로 채우기
| Python Programming |
'statwith'.zfill(15)
| Results |
'0000000statwith'
rjust() 함수
| Python Programming |
'statwith'.rjust(15,'*')
| Results |
'*******statwith'
| Python Programming |
(15-len('statwith'))*'*'+'statwith'
| Results |
'*******statwith'
rjust() 함수
| Python Programming |
emp['ename'].apply(lambda x : x.rjust(15,'*')).head(7)
| Results |
0 **********SMITH
1 **********ALLEN
2 ***********WARD
3 **********JONES
4 *********MARTIN
5 **********BLAKE
6 **********CLARK
Name: ename, dtype: object
3. R Programming (R Package)
stringr::str_pad() 함수
| R Programming |
%%R
stringr::str_pad('statwith', 15, side = c("left"), pad = "*")
| Results |
[1] "*******statwith"
stringr::str_pad() 함수
| R Programming |
%%R
stringr::str_pad(emp$ename, 15, side = c("left"), pad = "*")
| Results |
[1] "**********SMITH" "**********ALLEN" "***********WARD" "**********JONES"
[5] "*********MARTIN" "**********BLAKE" "**********CLARK" "**********SCOTT"
[9] "***********KING" "*********TURNER" "**********ADAMS" "**********JAMES"
[13] "***********FORD" "*********MILLER"
4. R Dplyr Package
stringr::str_pad() 함수
| R Programming |
%%R
emp %>%
dplyr::mutate(str_lpad = stringr::str_pad(ename, 15, side = c("left"), pad = "*")) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno str_lpad
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 **********SMITH
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 **********ALLEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 ***********WARD
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 **********JONES
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 *********MARTIN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 **********BLAKE
5. R sqldf Package
substr() 함수
| R Programming |
%%R
sqldf(" select substr('**************************' || 'statwith', -15, 15) str_lpad")
| Results |
str_lpad
1 *******statwith
substr() 함수
| R Programming |
%%R
sqldf(" select ename,
substr('**************************' || ename, -15, 15) str_lpad
from emp")
| Results |
str_lpad
1 **********SMITH
2 **********ALLEN
3 ***********WARD
4 **********JONES
5 *********MARTIN
6 **********BLAKE
7 **********CLARK
8 **********SCOTT
9 ***********KING
10 *********TURNER
11 **********ADAMS
12 **********JAMES
13 ***********FORD
14 *********MILLER
6. Python pandasql Package
substr() 함수
| Python Programming |
ps.sqldf(" select substr('**************************' || 'statwith', -15, 15) str_lpad ")
| Results |
str_lpad
0 *******statwith
substr() 함수
| Python Programming |
ps.sqldf(" select ename, substr('**************************' || ename, -15, 15) str_lpad from emp ").head()
| Results |
ename str_lpad
0 SMITH **********SMITH
1 ALLEN **********ALLEN
2 WARD ***********WARD
3 JONES **********JONES
4 MARTIN *********MARTIN
7. R data.table Package
stringr::str_pad() 함수
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, str_lpad := stringr::str_pad(ename, 15, side = c("left"), pad = "*")][, c('job','mgr','hiredate') := NULL]
| Results |
empno ename sal comm deptno str_lpad
1: 7369 SMITH 800 NA 20 **********SMITH
2: 7499 ALLEN 1600 300 30 **********ALLEN
3: 7521 WARD 1250 500 30 ***********WARD
4: 7566 JONES 2975 NA 20 **********JONES
5: 7654 MARTIN 1250 1400 30 *********MARTIN
6: 7698 BLAKE 2850 NA 30 **********BLAKE
7: 7782 CLARK 2450 NA 10 **********CLARK
8: 7788 SCOTT 3000 NA 20 **********SCOTT
9: 7839 KING 5000 NA 10 ***********KING
10: 7844 TURNER 1500 0 30 *********TURNER
11: 7876 ADAMS 1100 NA 20 **********ADAMS
12: 7900 JAMES 950 NA 30 **********JAMES
13: 7902 FORD 3000 NA 20 ***********FORD
14: 7934 MILLER 1300 NA 10 *********MILLER
8. Python DuckDB의 SQL
| Python Programming |
%%sql
SELECT LPAD('statwith',15,'*') LPAD_1,
substr('**************************' || 'statwith', -15, 15) str_lpad| Python Programming |
print( duckdb.sql(" SELECT LPAD('statwith',15,'*') LPAD_1, \
substr('**************************' || 'statwith', -15, 15) str_lpad ").df() )
| Results |
abs_1
15
| Python Programming |
%%sql
select ename,
LPAD(ename,15,'*') as str_lpad,
substr('**************************' || ename, -15, 15) str_lpad_1
from emp
LIMIT 6
| Python Programming |
duckdb.sql(" select ename, \
LPAD(ename,15,'*') as str_lpad, \
substr('**************************' || ename, -15, 15) str_lpad_1 \
from emp \
LIMIT 6 ").df()
| Results |
ename str_lpad str_lpad_1
0 SMITH **********SMITH **********SMITH
1 ALLEN **********ALLEN **********ALLEN
2 WARD ***********WARD ***********WARD
3 JONES **********JONES **********JONES
4 MARTIN *********MARTIN *********MARTIN
5 BLAKE **********BLAKE **********BLAKE
--------------------------------------------
| [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크 |
| 오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크 |
| [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글