CONCAT Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

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

---

[ CONCAT 함수 ]

 

---

CONCAT 함수는 char1과 char2를 연결하여 반환한다.

 

• 함수설명 : COALESCE 오라클 함수 링크

 

---

1. Oracle(오라클)

 

Oracle Programming

SELECT CONCAT(CONCAT(ENAME, '''s job category is '), JOB) "Job" 
FROM   EMP;

 

Results

		Job
----------------------------------------
KING's job category is PRESIDENT
BLAKE's job category is MANAGER
CLARK's job category is MANAGER
JONES's job category is MANAGER
SCOTT's job category is ANALYST
FORD's job category is ANALYST
SMITH's job category is CLERK
ALLEN's job category is SALESMAN
WARD's job category is SALESMAN
MARTIN's job category is SALESMAN
TURNER's job category is SALESMAN
ADAMS's job category is CLERK
JAMES's job category is CLERK
MILLER's job category is CLERK

 

---

2. Python Pandas(파이썬)

 

Python Programming

(emp['ename'] + "'s job category is " + emp['job']).head()

 

Results

0        SMITH's job category is CLERK
1     ALLEN's job category is SALESMAN
2      WARD's job category is SALESMAN
3      JONES's job category is MANAGER
4    MARTIN's job category is SALESMAN
dtype: object

 

 

 

---

3. R Programming (R Package)

 

R Programming

%%R

paste(emp$ename,"'s job category is " , emp$job )

 

Results

 [1] "SMITH 's job category is  CLERK"     "ALLEN 's job category is  SALESMAN" 
 [3] "WARD 's job category is  SALESMAN"   "JONES 's job category is  MANAGER"  
 [5] "MARTIN 's job category is  SALESMAN" "BLAKE 's job category is  MANAGER"  
 [7] "CLARK 's job category is  MANAGER"   "SCOTT 's job category is  ANALYST"  
 [9] "KING 's job category is  PRESIDENT"  "TURNER 's job category is  SALESMAN"
[11] "ADAMS 's job category is  CLERK"     "JAMES 's job category is  CLERK"    
[13] "FORD 's job category is  ANALYST"    "MILLER 's job category is  CLERK"   

 

 

 

---

4. R Dplyr Package

 

R Programming

%%R

emp %>%
  dplyr::mutate(ename_concat = paste(ename,"'s job category is " , job ) ) %>%
  dplyr::select( - c(hiredate,mgr) ) %>%
  head() 

 

Results

# A tibble: 6 x 7
  empno ename  job        sal  comm deptno ename_concat                       
  <dbl> <chr>  <chr>    <dbl> <dbl>  <dbl> <chr>                              
1  7369 SMITH  CLERK      800    NA     20 SMITH 's job category is  CLERK    
2  7499 ALLEN  SALESMAN  1600   300     30 ALLEN 's job category is  SALESMAN 
3  7521 WARD   SALESMAN  1250   500     30 WARD 's job category is  SALESMAN  
4  7566 JONES  MANAGER   2975    NA     20 JONES 's job category is  MANAGER  
5  7654 MARTIN SALESMAN  1250  1400     30 MARTIN 's job category is  SALESMAN
6  7698 BLAKE  MANAGER   2850    NA     30 BLAKE 's job category is  MANAGER  

 

---

 

R Programming

%%R

emp %>%
  dplyr::mutate( ename_concat_2 =  stringr::str_c(ename,"'s job category is " , job ) ) %>%
  dplyr::select( - c(hiredate,mgr) ) %>%
  head() 

 

Results

# A tibble: 6 x 7
  empno ename  job        sal  comm deptno ename_concat_2                   
  <dbl> <chr>  <chr>    <dbl> <dbl>  <dbl> <chr>                            
1  7369 SMITH  CLERK      800    NA     20 SMITH's job category is CLERK    
2  7499 ALLEN  SALESMAN  1600   300     30 ALLEN's job category is SALESMAN 
3  7521 WARD   SALESMAN  1250   500     30 WARD's job category is SALESMAN  
4  7566 JONES  MANAGER   2975    NA     20 JONES's job category is MANAGER  
5  7654 MARTIN SALESMAN  1250  1400     30 MARTIN's job category is SALESMAN
6  7698 BLAKE  MANAGER   2850    NA     30 BLAKE's job category is MANAGER  

 

 

 

---

5. R sqldf Package

 

R Programming

%%R

sqldf(" SELECT ename || '''s job category is ' || job as Job
        from emp  ")[1:5,]

 

Results

[1] "SMITH's job category is CLERK"     "ALLEN's job category is SALESMAN" 
[3] "WARD's job category is SALESMAN"   "JONES's job category is MANAGER"  
[5] "MARTIN's job category is SALESMAN"

 

 

 

---

6. Python pandasql Package

 

Python Programming

ps.sqldf(" SELECT ename || '''s job category is ' || job as Job    \
        from emp  ").head()

 

Results

	Job
0	SMITH's job category is CLERK
1	ALLEN's job category is SALESMAN
2	WARD's job category is SALESMAN
3	JONES's job category is MANAGER
4	MARTIN's job category is SALESMAN​

 

 

 

---

7. R data.table Package

 

R Programming

%%R

DT      <- data.table(emp)
dept_DT <- data.table(dept)

DT[, ename_concat := paste(ename,"'s job category is " , job )][1:5,-c("mgr","hiredate")]

 

Results

   empno  ename      job  sal comm deptno                        ename_concat
1:  7369  SMITH    CLERK  800   NA     20     SMITH 's job category is  CLERK
2:  7499  ALLEN SALESMAN 1600  300     30  ALLEN 's job category is  SALESMAN
3:  7521   WARD SALESMAN 1250  500     30   WARD 's job category is  SALESMAN
4:  7566  JONES  MANAGER 2975   NA     20   JONES 's job category is  MANAGER
5:  7654 MARTIN SALESMAN 1250 1400     30 MARTIN 's job category is  SALESMAN

 

---

 

R Programming

%%R

DT      <- data.table(emp)
dept_DT <- data.table(dept)

DT[, ename_concat_2 :=  stringr::str_c(ename,"'s job category is " , job )][1:5 , -c("mgr","hiredate")]

 

Results

   empno  ename      job  sal comm deptno                    ename_concat_2
1:  7369  SMITH    CLERK  800   NA     20     SMITH's job category is CLERK
2:  7499  ALLEN SALESMAN 1600  300     30  ALLEN's job category is SALESMAN
3:  7521   WARD SALESMAN 1250  500     30   WARD's job category is SALESMAN
4:  7566  JONES  MANAGER 2975   NA     20   JONES's job category is MANAGER
5:  7654 MARTIN SALESMAN 1250 1400     30 MARTIN's job category is SALESMAN

 

---

8. Python DuckDB의 SQL

 

Python Programming

%%sql
  SELECT CONCAT(CONCAT(ename, '''s job category is '), job) "Job"
  FROM   emp
  LIMIT 5

 

Python Programming

duckdb.sql(" SELECT CONCAT(CONCAT(ename, '''s job category is '), job) as Job_concat    \
             FROM   emp                                                                 \
             LIMIT 5 ").df()

 

Results

                          Job_concat
0      SMITH's job category is CLERK
1   ALLEN's job category is SALESMAN
2    WARD's job category is SALESMAN
3    JONES's job category is MANAGER
4  MARTIN's job category is SALESMAN

 

---

book store in deajeon (https://unsplash.com/photos/cJKfMvJGHD0)

 

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

 

[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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크