본문 바로가기
통계프로그램 비교 시리즈/오라클함수 비교(R & Python)

RTRIM 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

by 기서무나구물 2021. 12. 16.

포스팅 목차

    * 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

    [ RTRIM Oracle Function ]

     

    RTRIM함수는 문자열 char의 오른쪽 끝을 시작으로 검색하여서 문자열 set에서 지정한 모든 문자를 제거한다. 이 함수는 쿼리의 출력 포맷에 유용하다.

     

     

    1. Oracle(오라클)

     

    rtrim() 함수

    문자열 ‘XYXABCDEXYX’의 오른쪽으로부터 검색을 시작으로 문자열 ‘XY’이 검색되면 제거한다.

     

    Oracle Programming 
    SELECT RTRIM('XYXABCDEXYX', 'XY') RTRIM_FUNC
FROM   DUAL

     

    Results 
    RTRIM_FUNC
--------------
XYXABCDE

     

    2. Python Pandas(파이썬)

     

    rstrip 함수

    문자열 ‘STATWITH WITHMOOCxyxyxxyxy’의 오른쪽으로부터 검색을 시작으로 문자열 ‘XY’이 검색되면 반복하여 제거한다.

     

    Python Programming 
    'STATWITH WITHMOOCxyxyxxyxy'.rstrip('xy')

     

    Results 
    'STATWITH WITHMOOC'

     

    re.sub 함수

     

    Python Programming 
    re.sub(r'[xy]', '', 'STATWITH WITHMOOCxyxyxxyxy')

     

    Results 
    'STATWITH WITHMOOC'

     

    rstrip() 함수

    직무명을 오른쪽으로부터 검색하여서 직무명의 끝 부분에 ‘MAN’ 또는 ‘ER’이 검색되면 제거한다.

     

    Python Programming 
    emp['job'].apply(lambda x: x.rstrip(r'(MAN|ER)$')).head(7)

     

    Results 
    0    CLERK
1    SALES
2    SALES
3    MANAG
4    SALES
5    MANAG
6    MANAG
Name: job, dtype: object

     

    re.sub() 함수

     

    Python Programming 
    emp['job'].apply(lambda x: re.sub(r'(MAN|ER)$', '', x) ).head(7)

     

    Results 
    0    CLERK
1    SALES
2    SALES
3    MANAG
4    SALES
5    MANAG
6    MANAG
Name: job, dtype: object

     

     

     

    3. R Programming (R Package)

     

    stringi::stri_trim_right 함수

    문자열 ‘STATWITH WITHMOOCxyxyxxyxy’의 오른쪽으로부터 검색을 시작으로 문자열 ‘XY’이 검색되면 반복하여 제거한다.

     

    R Programming 
    %%R

stringi::stri_trim_right('STATWITH WITHMOOCxyxyxxyxy', pattern = "[^xy]")

     

    Results 
    [1] "STATWITH WITHMOOC"

     

    base::gsub 함수

     

    R Programming 
    %%R

base::gsub("[xy]", "", 'STATWITH WITHMOOCxyxyxxyxy')

     

    Results 
    [1] "STATWITH WITHMOOC"

     

    base::sub 함수

     

    R Programming 
    %%R

base::sub("[xy]+", "", 'STATWITH WITHMOOCxyxyxxyxy', perl = TRUE)

     

    Results 
    [1] "STATWITH WITHMOOC"

     

    base::gsub 함수

    직무명을 오른쪽으로부터 검색하여서 직무명의 끝 부분에 ‘MAN’ 또는 ‘ER’이 검색되면 제거한다.

     

    R Programming 
    %%R

base::gsub("(MAN|ER)$", "", emp$job)

     

    Results 
     [1] "CLERK"     "SALES"     "SALES"     "MANAG"     "SALES"     "MANAG"    
 [7] "MANAG"     "ANALYST"   "PRESIDENT" "SALES"     "CLERK"     "CLERK"    
[13] "ANALYST"   "CLERK"

     

    base::sub 함수

     

    R Programming 
    %%R

base::sub("(MAN|ER)$", "", emp$job, perl = TRUE)

     

    Results 
     [1] "CLERK"     "SALES"     "SALES"     "MANAG"     "SALES"     "MANAG"    
 [7] "MANAG"     "ANALYST"   "PRESIDENT" "SALES"     "CLERK"     "CLERK"    
[13] "ANALYST"   "CLERK"

     

    4. R Dplyr Package

     

    stringi::stri_trim_right 함수

    직무명을 오른쪽으로부터 검색하여서 직무명의 끝 부분에 ‘MAN’ 또는 ‘ER’이 검색되면 제거한다.

     

    R Programming 
    %%R

emp %>%
  dplyr::mutate(str_rtrim = stringi::stri_trim_right(job, pattern = "[^MAN|ER]")) %>%
  head()

     

    Results 
    # A tibble: 6 x 9
  empno ename  job        mgr hiredate     sal  comm deptno str_rtrim
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl> <chr>    
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20 CLERK    
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30 SALES    
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30 SALES    
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20 MANAG    
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30 SALES    
6  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30 MANAG

     

    base::sub 함수

     

    R Programming 
    %%R

emp %>%
  dplyr::mutate(str_rtrim = base::sub("(MAN|ER)$", "", job, perl = TRUE)) %>%
  head()

     

    Results 
    # A tibble: 6 x 9
  empno ename  job        mgr hiredate     sal  comm deptno str_rtrim
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl> <chr>    
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20 CLERK    
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30 SALES    
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30 SALES    
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20 MANAG    
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30 SALES    
6  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30 MANAG

     

    5. R sqldf Package

     

    rtrim 함수

    문자열 ‘STATWITH WITHMOOCxyxyxxyxy’의 오른쪽으로부터 검색을 시작으로 문자열 ‘xy’이 검색되면 반복하여 제거한다.

     

    R Programming 
    %%R

sqldf(" SELECT rtrim('STATWITH WITHMOOCxyxyxxyxy','xy') str_ltrim ")

     

    Results 
              str_ltrim
1 STATWITH WITHMOOC

     

    rtrim 함수

    직무명을 오른쪽으로부터 검색하여서 직무명의 끝 부분에 ‘MAN’ 또는 ‘ER’이 검색되면 제거한다.

     

    R Programming 
    %%R

sqldf(" SELECT JOB, RTRIM(job,'MAN|ER') str_ltrim from emp ")[1:10, ]

     

    Results 
             job str_ltrim
1      CLERK     CLERK
2   SALESMAN     SALES
3   SALESMAN     SALES
4    MANAGER     MANAG
5   SALESMAN     SALES
6    MANAGER     MANAG
7    MANAGER     MANAG
8    ANALYST   ANALYST
9  PRESIDENT PRESIDENT
10  SALESMAN     SALES

     

    6. Python pandasql Package

     

    rtrim 함수

     

    Python Programming 
    ps.sqldf("  SELECT rtrim('STATWITH WITHMOOCxyxyxxyxy','xy') str_ltrim  ")

     

    Results 
    	str_ltrim
0	STATWITH WITHMOOC

     

    rtrim 함수

     

    Python Programming 
    ps.sqldf(" SELECT JOB, RTRIM(job,'MAN|ER') str_ltrim from emp ").head(7)

     

    Results 
    	job		str_ltrim
0	CLERK		CLERK
1	SALESMAN	SALES
2	SALESMAN	SALES
3	MANAGER		MANAG
4	SALESMAN	SALES
5	MANAGER		MANAG
6	MANAGER		MANAG

     

     

     

    7. R data.table Package

     

    stringi::stri_trim_right() 함수

     

    R Programming 
    %%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)
DT[,str_rtrim := stringi::stri_trim_right(job, pattern = "[^MAN|ER]")][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno str_rtrim
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     CLERK
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     SALES
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     SALES
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     MANAG
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     SALES
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     MANAG
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     MANAG
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20   ANALYST
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10 PRESIDENT
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     SALES

     

    8. Python Duckdb의 SQL

     

    Python Programming 
    %%sql
  SELECT RTRIM('XYXABCDEXYX', 'XY') as str_rtrim

     

    Python Programming 
    duckdb.sql(" SELECT RTRIM('XYXABCDEXYX', 'XY') as str_rtrim ").df()

     

    Results 
      str_rtrim
0  XYXABCDE

     

     

    Python Programming 
    %%sql
  SELECT rtrim('STATWITH WITHMOOCxyxyxxyxy','xy') str_rtrim

     

    Python Programming 
    duckdb.sql(" SELECT rtrim('STATWITH WITHMOOCxyxyxxyxy','xy') str_rtrim ").df()

     

    Results 
               str_rtrim
0  STATWITH WITHMOOC

     

     

    Python Programming 
    %%sql
  SELECT JOB, RTRIM(job,'MAN|ER') str_ltrim
  from   emp
  LIMIT  6

     

    Python Programming 
    duckdb.sql(" SELECT JOB, RTRIM(job,'MAN|ER') str_ltrim   \
             from   emp                                  \
             LIMIT  6 ").df()

     

    Results 
            job str_ltrim
0     CLERK     CLERK
1  SALESMAN     SALES
2  SALESMAN     SALES
3   MANAGER     MANAG
4  SALESMAN     SALES
5   MANAGER     MANAG

     

    ( https://unsplash.com/photos/R-srioOZAIU )

      --------------------------------------------  

     

     

    [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크
    반응형
    저작자표시 비영리 변경금지

    '통계프로그램 비교 시리즈 > 오라클함수 비교(R & Python)' 카테고리의 다른 글

    SIGN 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.17
    SESSIONTIMEZONE / SYSTIMESTAMP 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.17
    RPAD 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.15
    ROW_NUMBER 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.15
    ROUND(date) 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.15

    관련글

    댓글

    티스토리툴바