포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ REPLACE Oracle Function ]
REPLACE 함수는 입력한 문자열(char)에서 검색하기 위한 문자열(search-string)을 조회하여 일치하는 문자열을 변경 문자열(replacement_string)로 변경한다. 만약 replacement_string가 생략되거나 Null 값이면, 입력한 문자열(char)에서 search_string을 제거한다. 만약 search_string가 Null이면, char을 그대로 반환한다.
- 함수 설명 : REPLACE 오라클 함수 링크
1. Oracle(오라클)
REPLACE() 함수
입력한 문자열(‘ABCDEFG’)을 검색하여서 ‘CD’ 문자열과 일치하는 부분을 ‘statwith’로 변경한다.
| Oracle Programming |
SELECT REPLACE('ABCDEFG', 'CD', 'statwith') REPLACE_FUNC
FROM DUAL
| Results |
REPLACE_FUNC
--------------------
ABstatwithEFG
2. Python Pandas(파이썬)
REPLACE() 함수
| Python Programming |
'ABCDEFG'.replace("CD", "statwith")
| Results |
'ABstatwithEFG'
3. R Programming (R Package)
gsub() 함수
| R Programming |
%%R
gsub("CD", "statwith", "ABCDEFG")
| Results |
[1] "ABstatwithEFG"
4. R Dplyr Package
gsub() 함수
| R Programming |
%%R
emp %>%
dplyr::mutate( job_replace = gsub("MAN", "WOMEN", job)) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno job_replace
<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 SALESWOMEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESWOMEN
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 WOMENAGER
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALESWOMEN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 WOMENAGER
stringr::str_replace() 함수
| R Programming |
%%R
emp %>%
dplyr::mutate( job_replace = stringr::str_replace(job, "MAN", "WOMEN")) %>%
head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno job_replace
<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 SALESWOMEN
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESWOMEN
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 WOMENAGER
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALESWOMEN
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 WOMENAGER
5. R sqldf Package
replace() 함수
| R Programming |
%%R
sqldf(" SELECT REPLACE('ABCDEFG', 'CD', 'statwith') str_replace ")
| Results |
str_replace
1 ABstatwithEFG
6. Python pandasql Package
replace() 함수
| Python Programming |
ps.sqldf(" SELECT REPLACE('ABCDEFG', 'CD', 'statwith') str_replace ")
| Results |
str_replace
0 ABstatwithEFG
7. R data.table Package
stringr::str_replace() 함수
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, str_replace := stringr::str_replace(job, "MAN", "WOMEN") ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno str_replace
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 SALESWOMEN
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESWOMEN
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 WOMENAGER
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALESWOMEN
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 WOMENAGER
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 WOMENAGER
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 SALESWOMEN
gsub() 함수
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, str_replace := gsub("MAN", "WOMEN", job) ][1:10, ]
| Results |
empno ename job mgr hiredate sal comm deptno str_replace
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 SALESWOMEN
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESWOMEN
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 WOMENAGER
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALESWOMEN
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 WOMENAGER
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 WOMENAGER
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 SALESWOMEN
gsub() 함수
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
DT[, str_replace := lapply(.SD, function(x) gsub("MAN", "WOMEN", x)), .SDcols = c("job") ][1:10, ]
empno ename job mgr hiredate sal comm deptno str_replace
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 CLERK
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 SALESWOMEN
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 SALESWOMEN
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 WOMENAGER
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 SALESWOMEN
6: 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 WOMENAGER
7: 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 WOMENAGER
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 SALESWOMEN
8. Python Duckdb의 SQL
REPLACE() 함수
| Python Programming |
%%sql
SELECT REPLACE('ABCDEFG', 'CD', 'statwith') as str_replace
| Python Programming |
duckdb.sql(" SELECT REPLACE('ABCDEFG', 'CD', 'statwith') as str_replace ").df()
| Results |
str_replace
0 ABstatwithEFG
--------------------------------------------
| [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글