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

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

---

[ LOWER Oracle Function ]

 

---

LOWER 함수는 입력된 문자열을 소문자로 변환한다.

 

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

 

---

1. Oracle(오라클)

lower 함수를 사용하여서 사원 이름을 모두 소문자로 전환하여 출력한다.

 

Oracle Programming

select lower(ename) ename_lower
from   emp

 

Results

ENAME_LOWER
-------------
king
blake
clark
jones
scott
ford
smith
allen
ward
martin
turner
adams
james
miller

 

---

2. Python Pandas(파이썬)

 

LOWER() 함수

Python Programming

withmooc = copy.copy(emp)

withmooc['ename_low'] = withmooc['ename'].str.lower()

withmooc[['ename','job','ename_low']].head()

 

Results

	ename	job		ename_low
0	SMITH	CLERK		smith
1	ALLEN	SALESMAN	allen
2	WARD	SALESMAN	ward
3	JONES	MANAGER		jones
4	MARTIN	SALESMAN	martin

 

 

---

3. R Programming (R Package)

 

Tolower() 함수

R Programming

%%R

withmooc<-emp

withmooc['ename_low'] = tolower(withmooc$ename)

withmooc[1:10, ]

 

Results

# A tibble: 10 x 9
   empno ename  job         mgr hiredate     sal  comm deptno ename_low
   <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    
 7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10 clark    
 8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20 scott    
 9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10 king     
10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30 turner   

 

 

---

4. R Dplyr Package

 

stringr::str_to_lower()

R Programming

%%R

emp %>%
  dplyr::mutate(ename_low = tolower(ename), ename_low_1 = stringr::str_to_lower(ename)) %>%
  head()

 

Results

# A tibble: 6 x 10
  empno ename  job       mgr hiredate     sal  comm deptno ename_low ename_low_1
  <dbl> <chr>  <chr>   <dbl> <date>     <dbl> <dbl>  <dbl> <chr>     <chr>      
1  7369 SMITH  CLERK    7902 1980-12-17   800    NA     20 smith     smith      
2  7499 ALLEN  SALESM~  7698 1981-02-20  1600   300     30 allen     allen      
3  7521 WARD   SALESM~  7698 1981-02-22  1250   500     30 ward      ward       
4  7566 JONES  MANAGER  7839 1981-04-02  2975    NA     20 jones     jones      
5  7654 MARTIN SALESM~  7698 1981-09-28  1250  1400     30 martin    martin     
6  7698 BLAKE  MANAGER  7839 1981-03-01  2850    NA     30 blake     blake      

 

 

---

5. R sqldf Package

 

Lower() 함수

lower 함수를 사용하여서 사원 이름을 모두 소문자로 전환하여 출력한다.

 

R Programming

%%R

sqldf(" select ename,
               lower(ename) ename_low 
        from   emp ")

 

Results

    ename ename_low
1   SMITH     smith
2   ALLEN     allen
3    WARD      ward
4   JONES     jones
5  MARTIN    martin
6   BLAKE     blake
7   CLARK     clark
8   SCOTT     scott
9    KING      king
10 TURNER    turner
11  ADAMS     adams
12  JAMES     james
13   FORD      ford
14 MILLER    miller

 

 

---

6. Python pandasql Package

 

Lower() 함수

lower 함수를 사용하여서 사원 이름을 모두 소문자로 전환하여 출력한다.

 

Python Programming

ps.sqldf(" select ename, lower(ename) ename_low from emp ").head()

 

Results

	ename	ename_low
0	SMITH	smith
1	ALLEN	allen
2	WARD	ward
3	JONES	jones
4	MARTIN	martin

 

 

---

7. R data.table Package

 

Tolower() 함수와 stringr::str_to_lower() 함수

R Programming

%%R

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

DT[, `:=`( ename_low   = tolower(ename) , 
           ename_low_1 = stringr::str_to_lower(ename))][, c("job","hiredate","comm") := NULL]

 

Results

    empno  ename  mgr  sal deptno ename_low ename_low_1
 1:  7369  SMITH 7902  800     20     smith       smith
 2:  7499  ALLEN 7698 1600     30     allen       allen
 3:  7521   WARD 7698 1250     30      ward        ward
 4:  7566  JONES 7839 2975     20     jones       jones
 5:  7654 MARTIN 7698 1250     30    martin      martin
 6:  7698  BLAKE 7839 2850     30     blake       blake
 7:  7782  CLARK 7839 2450     10     clark       clark
 8:  7788  SCOTT 7566 3000     20     scott       scott
 9:  7839   KING   NA 5000     10      king        king
10:  7844 TURNER 7698 1500     30    turner      turner
11:  7876  ADAMS 7788 1100     20     adams       adams
12:  7900  JAMES 7698  950     30     james       james
13:  7902   FORD 7566 3000     20      ford        ford
14:  7934 MILLER 7782 1300     10    miller      miller

 

---

8. Python DuckDB의 SQL

 

Python Programming

%%sql
  select ename,
         lower(ename) ename_lower
  from   emp
  LIMIT  6

 

Python Programming

duckdb.sql(" select ename,                       \
                    lower(ename) ename_lower     \
             from   emp                          \
             LIMIT  6 ").df()

 

 

Results

    ename ename_lower
0   SMITH       smith
1   ALLEN       allen
2    WARD        ward
3   JONES       jones
4  MARTIN      martin
5   BLAKE       blake

 

 

---

Korean Food Bibimbap with Kimchi (https://unsplash.com/photos/4f4YZfDMLeU)

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

 

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