[SQL] 문자 함수로 데이터 가지고 놀기 - ORDER BY, INITCAP, LOWER, UPPER, LENGTH, LENGTHB, SUBSTR, INSTR, LPAD, RPAD, TRIM, REPLACE, TRANSLATE

오늘도 열심히 SQL과 달려봅시다!

 

아직 Select, From, Where 절 사용이 미숙하시다면 아래 글들을 참고해주세요!

[SQL] SELECT, FROM 으로 데이터 조회하기  <- 이동

[SQL] WHERE 로 원하는 조건에 해당하는 데이터 조회하기 <- 이동

[SQL] 다양한 연산자 - between and, in, like, is null <- 이동

 

 

 1. order by를 사용한 정렬

 

먼저 ORDER BY는 ~로 정렬하다 라는 의미를 가지고 있죠?

ORDER BY 절은 select절의 맨 마지막에 나와야 합니다.

default 정렬은 오름차순(ASC)으로 등록되어있고 , 내림차순으로 바꾸고싶다면 DESC를 사용하면 됩니다. 

정렬 가능한 데이터 타입은 한글, 영어, 숫자, 날짜 모두 가능하고말고요!

또 하나 알아야 할 것은 2차 정렬조건 사용 시, 1차 정렬조건을 만족(조건 값이 중복될 때)해야 2차 정렬조건이 수행된다고 하네요.

 

=> STUDENT 테이블에서 grade가 작은순으로 정렬(ASC는 디폴트이므로 생략 가능)

select *

from STUDENT

order by GRADE;

 

 

=> STUDENT 테이블에서 grade가 작은순, 같은 학년 내에선 birthday가 느린순으로 정렬

select *

from STUDENT

order by GRADE, BIRTHDAY desc;

 

 

만일, STUDENT 테이블에서 학번이 작은순으로 정렬하고, 학번이 같을 경우 한년순으로 정렬할 때,

select *

from STUDENT

order by STUDNO, GRADE;

학번은 중복될 수 없는 데이터이므로 2차 정렬조건이 불필요하게 됩니다. 성능상으로도 비효율적이고요! 

정렬은 메모리를 많이 차지하는 함수이므로 불필요할 때는 사용하지 않는게 좋다고 합니다.

그러므로 2차 정렬조건은 1차 조건 정렬 후에 중복된 데이터가 있을 경우 사용하기!

 

 

2. 함수 

 

 

 함수는 다 외워줘야 할 정도로 중요하다고 해요.. 많이 사용하다보면 자연스럽게 외워지겠죠..?

 먼저 함수에서 단일 행 함수는 하나의 행 데이터가 1:1로 치환되어 리턴.

 복수 행 함수는 여러개가 묶여서 단 하나의 값을 리턴. 예로 Average, Sum 과 같은 함수가 있겠죠?

 단일 행 함수의 목적은 치환, 변경, 조작 등이 있고, 복수 행 함수의 목적은 연산으로 많이 사용된다고 해요.

 

여기서 단일 행 함수는 여러번 사용할 수 있고, 그 말은 즉슨 중복사용도 가능하다는 말이죠.

사용 가능한 절은 select, where, order by 입니다.

 

 

 1. 문자 함수

 

 

 문자 함수란 문자열로 구성되어있는 함수에 사용할 수 있는 함수인데요.

 

 

1-1. INITCAP, LOWER, UPPER 함수

 

 

먼저,  select문에 대소 치환하여 출력하고 싶을 때 사용합니다.

 

 select ENAME,    // 원본 데이터 그래도 출력

        initcap(ENAME),  // 첫 문자만 대문자 치환

        lower(ENAME),   // 전체 소문자 치환

        upper(ENAME)   // 전체 대문자 치환

  from EMP;

 

 

데이터를 치환하여 비교하고 싶다면 where절에 사용할 수 있습니다.

 select ename, sal

   from emp

  where lower(ename) = 'smith';

 

 

이러면 원본 데이터의 ename 컬럼에 initcap 형식이나, upper 형식으로 저장된 데이터들도 

다 소문자로 치환되므로 비교하기 더 쉬워지겠죠?

 

소문자로 변경 후 정렬도 가능해요,

원본 데이터로 정렬하고싶지 않을 경우, 변경된 데이터 기준으로 정렬하는거죠.

select ename, sal

from emp

order by lower(ename);

 

 

1-2. LENGTH, LENGTHB 함수

  

LENGTH 는 글자 수를 리턴해주는 함수,

LENGTHB 는 글자의 바이트 수를 리턴해주는 함수에요.

 

select ename, length(ename), lengthb(ename)

from emp;

 

영어는 문자 하나에 1byte 이므로, length 함수와 같은 결과가 나오겠지만,

한글은 문자 하나에 2byte이므로, 영어와는 다른 결과가 나올겁니다!

 

언제 사용할지 싶은데 종종 필요할 때가 있다고 하네요!

 

 

1-3. SUBSTR 함수 ★★★

 

 

SQL에서 가장 많이 사용되는 함수이므로 굉장히 중요하다고 합니다. 머리에 꽉꽉 넣어보자고요!

 

먼저, substr 함수는 원하는 문자를 추출하는 목적, 문자열의 일부 데이터만 필요한 경우 사용됩니다.

예를 들어보자면 주민등록번호에서 성별 추출, 전화번호에서 지역번호 추출, 생년월일에서 연도만 추출 등이 있겠네요. 여기서 추출된 데이터를 분석에 적용한다면 성별분석, 지역분석, 연도분석을 하기 위해서도 얼만큼 중요한 함수인지 감이 오네요..

 

substr 함수의 문법은

substr(원본문자열 혹은 컬럼, 시작위치, 추출개수)

이렇게 되는데요. 추가적인 간단한 정보로

  - 시작위치 < 0 : 뒤에서부터 추출

  - 추출개수 생략 시 끝까지 추출

정도(?)가 있습니다.

 

이제 SQL문에 적용해볼까요?!

 

=>stydent 테이블의 name 컬럼에서 성만 출력

   name 컬럼의 1 번째 위치에서 1 개의 문자를 추출하겠다는 의미이죠.

select name, substr(name, 1, 1) as 성

from student;

 

 

=> stydent 테이블의 name 컬럼에서 성, 이름을 따로 출력

select name,

       substr(name,1,1) AS 성,

       substr(name, 2, 2) as 이름,

       substr(name, 2) as 이름2   // 끝까지 추출하려면 마지막 인자는 생략 가능

from student;

 

 

=> student 테이블에서 각 학생의 이름, 성별 출력(1 또는 2)

   성별은 주민등록번호를 활용하면 됩니다.

   jumin 컬럼의 7 번째 위치에서 1 개의 문자를 추출한다는 의미죠.

select name, substr(jumin, 7,1) AS 성별

from student;

 

 

=> student 테이블에서 남학생의 정보만 출력

select *

  from student

 where substr(jumin, 7,1) = 1;

 

 

=> student 테이블에서 전화번호컬럼의 마지막 네자리 전화번호만 추출

시작위치가 음수인 경우 시작점은 뒤에서부터 고려해주지만 추출은 무조건 정방향으로 진행된다는 점! 

기억해주셔야 합니다.

select tel, substr(tel,-4) AS "4자리 전화번호"

from student;

 

 

substr(tel,9) 를  사용하실 수 있지만 이렇게 되면 전화번호의 길이가 다를 경우, 의도하던 원하는 데이터가 나오지 않을 수 있습니다. 그러므로 정확히 함수를 사용해서 추출해주는게 좋죠.

 

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

 

* 잠시 다른 이야기로

dual 테이블은 sql을 사용하다가 test 해볼 때 좋은 테이블입니다.

실제 존재하지 않은 가상의 테이블로,  꼭 저장되어있는 데이터로 공부할 필요가 없고 dual 테이블을 이용해서 공부를 하면 편리하답니다.

select sysdate // 현재 연/월/일 시간을 출력해주는 함수.

  from dual; 

 

 

select 360*11

  from dual;

 

=> 문자열 'abcdefg' 에서 'de' 추출

select 'abcdefg', substr('abcdefg', -4,2) AS test

  from dual;

 

여기서 잠시 하나 알아야 할 것은

날짜에서 특정 연, 월, 일을 추출하기 위해 substr을 사용하면 좋지 않다는 점이에요.

SQL (select, from, where) 이라는 제목의 글에서도 말씀드렸었는데 원본에 저장되어있는 날짜 포맷을 꼭 먼저 확인한 후 사용해야 하기 때문이죠.

 

2018/12/14 형식인지, 18/12/14 형식인지.

원본은 18/12/14로 저장되어있더라도 프로그램 포맷에 따라 2018/12/14 형식으로 출력되는 경우가 있기 때문이죠. 제가 사용하는 Orange 프로그램은 YYYY/MM/DD 형식으로 출력해주고 있어요!

 

DB당 날짜 포맷은 하나이고 DB마다 날짜 포맷이 다르니 꼭 먼저 확인해야합니다.

확인 방법은 사용하는 DB에 직접 접속해서 날짜 타입의 데이터를 확인해보면 됩니다.

 

앞에서 보았던

select sysdate // 현재 연/월/일 시간을 출력해주는 함수.

  from dual; 

이 SQL문을 사용해보면 간편하겠죠~?

 

그래도 굳이! substr로 날짜 데이터를 이용하여 추출하고 싶다고 한다! 그렇다면

만일, 원본 데이터 형식이 18/12/14로 저장되어있고 2018로 연도를 출력하고싶다면

select sysdate, 20 || substr(sysdate,1,2) AS year

  from dual;

 

이렇게 해주시면 됩니다. 위 SQL문의 결과로 18이 출력되기 때문에 2018로 맞춰주기 위해

앞에 연결 연산자를 사용해서 20과 함께 출력해주면 2018이 만들어지겠죠?!

 

원본 데이터 형식을 확인했으니 연, 월, 일 을 추출해볼까요?

select sysdate,

    20 || substr(sysdate,1,2) AS year,

    substr(sysdate,4,2) AS month,

    substr(sysdate,7,2) AS day

  from dual;

 

 

날짜 함수를 사용하여 날짜 데이터를 추출하면 훨씬 편리하지만, 그래도! substr 함수로 날짜를 추출하겠다! 

라고 할 때, 

 

날짜 포맷을 저장되어있는 날짜 형식과 상관없이 이렇게 임시적으로 사용하겠다고 지정할 수 있어요. 

다만 이것은 DB에 접속해서 세션을 변경해줘야하는 귀찮음(?)이 있을 수도..

 

SQL> alter session set nls_date_format='YY/MM/DD';

 

 

 

 

 1-4. INSTR 함수 ★★

 

 이 함수도 정말 중요해요!

어떤 특정 위치 전까지, 특정 위치 부터

이렇게 위치를 기반으로 이전, 이후의 문자들을 추출할 목적으로 주로 사용해요

 

앞에서 배운 SUBSTR과 INSTR의 조합은 정말~ 중요하다고 하네요

이 둘을 조합해서 어디에 사용할 수 있냐!

 

예를 들어,

1. student 테이블의 tel 컬럼을 사용하여 각 학생의 지역번호 추출

    -  ' ) ' 이전까지의 문자(지역번호) 추출

    - 031)123-4567 라고 한다면 '031'을 추출하겠죠?

2. student 테이블의 email 컬럼을 사용하여 각 학생의 이메일 아이디 추출

    -  ' @ ' 이전까지의 문자(이메일 아이디) 추출

    - data@tistory.com 라고 한다면 'data'를 추출하겠죠?

 

INSTR은 다음과 같이 사용합니다.

 

INSTR(추출 문자열 또는 컬럼, 찾고자하는 문자열, 시작위치, 반복번호)

 

만일 찾고자하는 문자가 없으면 0을 리턴,

이것은 특정 문자가 포함되어있지 않는 데이터를 추출할 때 사용할 수 있겠죠?

단순히 포함 여부를 확인 시에도 사용합니다.

예를 들자면 email ID에 생년월일이 포함되어있는지 확인하고 싶을 때(?) 라고 칩니다..

 

시작 위치 생략 시에는 처음부터 시작,

반복번호 생략 시에는 default로 최초 위치,

 

직접 해보는게 이해가 빠르겠네요!

 

select 'ab@cd@ef',

        instr('ab@cd@ef','@',1,1) AS "첫 번째 발견되는 @의 위치",

        instr('ab@cd@ef','@',1,2) AS "두 번째 발견되는 @의 위치"

  from dual;

첫 번째 예시는, 첫(1) 문자부터 처음으로(1) 발견되는 문자열의 위치를 출력해줍니다. 

두번째 예시는, 첫(1) 문자부터 두 번째로(2) 발견되는 문자열의 위치를 출력해줍니다.

 물론, 시작 위치에 따라서 결과를 다르게 표현할 수도 있어요!

 

 

=> ' ) ' 이전까지의 지역번호 추출

select NAME,

       substr(TEL, 1, 3) AS 지역번호1,

       instr(TEL, ')', 1, 1) AS ")의 위치",

       instr(TEL,')',1,1)-1 AS "추출 개수",

       substr(TEL, 1,  instr(TEL,')', 1, 1)-1) AS 지역번호2

  from student;

 

 

 

SUBSTR을 사용할 경우 지역번호가 '02'같은 경우는 길이가 다르기 때문에 '02)' 처럼 원하지 않는 데이터까지 추출될 수가 있죠.

그래서 ' ) ' 의 위치는 INSIR 함수로 찾아주고 ' ) ' 의 위치에서 1을 뺀 위치,

만일 1을 빼지 않으면 ' ) ' 까지 추출되기 때문이죠!

정리해보자면 TEL 컬럼의 첫 문자(1) 부터 ' ) ' 의 전 위치까지 출력해주면 지역번호 2 컬럼처럼

원하는 데이터가 추출되게 됩니다!

 

 

 

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

 

다음으로는 만일 데이터에 공백이 있다면, 데이터 검색 시 제대로 검색이 안될 수 있습니다.

그렇기때문에 공백을 가지고있는 데이터를 조회해볼 수 있는데요,

 

=> id컬럼의 공백의 위치 조회

select instr(id, ' ')

from student;

* 만일 데이터에 공백이 없다면 0을 리턴해주겠죠?

 

 

=> id컬럼에 공백을 가지고있는 데이터를 조회

select *

from student

where instr(id, ' ') != 0;

* 데이터에 공백이 없을 시, 0을 리턴해주므로

공백을 가지고있는 데이터를 조회하려면 ' != ' 을 사용해야 합니다.

 

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

 

다음으로 또 중요한 것! 말로 설명이 어렵지만.. 잘 이해해야합니다!!

바로 위치를 찾은 후 스캔의 방향인데요!

 

INSTR에서 시작 위치가 음수면 맨 끝 위치에서부터 왼쪽 방향으로 위치를 찾고,

문자 스캔도 왼쪽 방향(역방향)으로 스캔합니다. 

 

앞에서 배운 SUBSTR은  시작 위치가 음수면 경우 맨 끝 위치부터 왼쪽 방향으로 위치는 찾고,

문자 스캔은 오른쪽 방향(정방향)으로 스캔합니다.

 

select 'abcabcabc',
        instr('abcabcabc', 'c', -3,1),
        substr('abcabcabc', -3, 3)
from dual;

 

이해가.. 안되시죠?

자, 먼저 INSTR입니다.

   

instr('abcabcabc', 'c', -3,1)

시작 위치가 -3 이므로 뒤에서부터 왼쪽 방향으로 위치를 찾습니다. 7번째에 있는 'a'겠죠?

그 다음! 문자 스캔도 왼쪽(역방향)으로 하는겁니다. 7번째 'a'에서 첫 번째(1) 로 나오는 문자 'c'의 위치! 

그래서 6번째에 있는 'c'가 스캔되어 6이 리턴되는 것입니다.

 

다음 SUBSTR

substr('abcabcabc', -3, 3)

시작 위치가 -3 이므로 마찬가지로 뒤에서부터 왼쪽 방향으로 위치를 찾습니다.

7번째에 있는 'a'.

 

그 다음! 이번에는 문자 스캔을 오른쪽(정방향)으로 하는겁니다. 7번째 'a'부터 3개의 문자 추출.

그래서 'abc'가 추출되는거죠.

 

나중에 대량의 데이터를 추출할 때, 일일이 함수를 다 실행시켜볼 수 없는데

스캔 방향에 대한 차이를 모르고 쿼리를 실행했을 때, 계속 오류가 발생할텐데, 어디서 나는지는 모르겠고.. 

이런 상황을 대처하기 위해! 너무 TMI로 설명하긴 했는데..  

 

 

 

  1-5. LPAD, RPAD 함수

 

 이 함수는 문자열 앞, 혹은 뒤에 특정 문자열을 삽입하고 싶은 경우 사용됩니다.

LPAD는 왼쪽, RPAD는 오른쪽, Left, Right

 

LPAD(추출 문자열 또는 컬럼, 자리수, '채울 문자')

 

채울 문자를 생략 시 문자대신 공백이 삽입됩니다.

자리수는 문자 몇 개를 채울까가 아니라!! 총 몇 자리를 만들건지 정하는 옵션이에요.

잘 못 사용했다간 데이터가 손실된 상태로 출력되는수가..!

 

양 옆에 문자를 삽입하고 싶다면,  LPAD와 RPAD를 같이 활용해서 해주면 되요.

 

=> 총 여섯자리의 문자열을 만들껀데 부족한 자리 수는 왼쪽부터 *로 채우기

select 'abcde',

        lpad('abcde',6,'*'),  

        lpad('abcde',4,'*')  

from dual;

앞서 말했듯이 자리수는 총 몇 자리의 문자열을 만들건지 정하는 옵션이에요!

위 예시처럼 4개의 문자를 삽입하고 싶다고, 자리수에 4를 입력하면 오히려 데이터가 손실되죠..

 

만일, 기존의 데이터에서 무조건 4개의 *을 삽입하고 싶은 경우,

기존의 데이터 길이(length) + 붙이고자하는 별표의 개수를 자리수에 넣어주면 되요.

Length의 함수가 이럴 때 사용되는군요!

select id, lpad(id, length(id)+4, '*')

from student;

 

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

 

  1-6. TRIM 함수

 

 

TRIM 함수는 문자 제거를 담당하는 함수이지만 조립하기위해 자주 사용하고, 마찬가지로  중요합니다.!!

 

방향에 따라 LTRIM, RTRIM 함수를 사용하여 특정 문자를 제거합니다.

L과 R이 빠진 TRIM 함수를 사용하면 양옆에서 동시에 특정 문자를 제거할 수 있습니다. 다만, LTRIM, RTRIM과 다른 점은 오로지 공백 제거 목적으로 주로 사용됩니다.

 

LTRIM(추출 문자열 또는 컬럼, '제거할 문자')

RTRIM(추출 문자열 또는 컬럼, '제거할 문자')

 

이 세 가지 TRIM 함수들은 중간에 있는 문자는 제거할 수 없고, 오로지 왼쪽 혹은 오른쪽에 있는 특정 문자만 제거 가능합니다. 제거하려는 특정 문자가 아닌 다른 문자가 스캔되면 제거 과정을 멈춤니다.

 

select 'abcabc', ltrim('abcabc', 'a')

from dual;

LTRIM 이니까 왼쪽 방향부터 특정 문자의 제거가 시작되는데 첫 번째 a가 제거되고 b가 등장하자 제거 과정이 멈춰버리고, 첫 번째 a만 제거된 결과가 나온 것을 확인할 수 있습니다.

 

TRIM 함수는 공백 제거 목적이므로 두번째 인자를 받지 않고, 오로지 공백만 제거해줍니다. 

 

TRIM(추출 문자열 또는 컬럼)

 

데이터 조회 시 trim함수를 사용해주면 좋은데, 이는 데이터에 공백이 포함되어있을 경우가 종종 있기 때문입니다.

 

select 'abcabc', trim('abcabc'), trim(' abc ')

from dual;

' abc ' 에서 양 옆에 공백이 제거된 것이 티가 잘 안나지만, Length 함수로 비교해보면 확인 가능합니다.

 

공백이 삽입되어 있는 데이터의 경우 조회가 잘 되지 않은 현상이 있는데,

먼저 emp 테이블에 이름 앞뒤 공백을 갖는 행 하나를 삽입해봅니다.

 

insert into emp(empno, ename, job) values(9999, ' PARK ', 'CLERK');

commit;

 

commit은 저장 기능을 갖는 명령어인데,

데이터를 insert 해서 메모리에 있는 데이터를 디스크에 써주는 작업을 해줍니다.

 

이처럼 데이터에 PARK 정보가 있는것을 확인했는데

 

조건을 주고 검색했을 때, 나오지 않는 경우 99.9% 공백 때문!!!

이럴 경우, trim을 활용하면 아주 좋겠죠?

 

select *

from emp

where trim(ename) = 'PARK';

 

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

 

  1-7. REPLACE, TRANSLATE  함수

 

 

REPLACE, TRANSLATE 함수도 현업에서 정말 많이 사용된다고해요.

R, Python에서도 사용하는 함수니까 여기서 머릿속에 쑥쑥 넣어두면 좋겠죠?

 

먼저, REPLACE, TRANSLATE 함수는 문자를 치환해주는 함수에요.

 

REPLACE('문자열 또는 컬럼', '찾을 문자열', '바꿀 문자열')

TRANSLATE ('문자열 또는 컬럼', '찾을 문자열', '바꿀 문자열')

 

replace는 단어 단위의 치환, translate는 문자의 1:1 치환

 

단어의 치환이란, 바꿀 문자열과 완벽히 일치하는 단어만 치환해주는 것입니다.

반대로 문자의 1:1 치환은, 문자를 1:1로 치환해준다는 의미죠.

 

translate는 치환 뿐만아니라 삭제 기능도 가지고 있습니다.

 

바로 예제로 가보시죠! 

 

select replace('abcba', 'a', 'A'), replace('abcba', 'ab', 'AB')

from dual;

첫 번째 결과를 보면, replace는 단어의 치환이므로 '찾을 문자열'과 '바꿀 문자열'을 단어로 인식합니다.

따라서 'a' 라는 단어를 'A' 로 잘 치환해주지만

두 번째 결과를 보면, 'ab' 를 단어로 인식해서 'ab' 라는 단어만 'AB'로 치환해줍니다.

 

translate 함수와 비교해보면,

 

select replace('abcba', 'ab', 'AB'), translate('abcba', 'ab', 'AB')

from dual;

replace 함수는 'ab' 와 완벽하게 매칭하는 단어를 'AB'로 치환해준 반면,

translate 함수는 1:1 치환이므로 'a'는 'A'로 'b'는 'B'로 치환해주는 것을 확인할 수 있죠.

 

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

 

다음으로, 치환함수를 사용한 문자의 삭제입니다.

 

translate 함수에서 바꿀 문자열을 공백으로 두면 1:1 매칭이 일어나지 않기때문에 데이터가 다 삭제되버립니다.

 

select replace('abcba', 'a', ''), translate('abcba', 'a', '')

from dual;

replace 함수는 'a' 를 모두 공백으로 치환해주었지만,

translate 함수는 데이터가 다 삭제되버렸습니다...

 

여기서 약간의 꼼수(?)를 사용하면, 특정 문자만 삭제할 수 있습니다.

 

select replace('abcba', 'a', ''), translate('abcba', '1a', '1')

from dual;

translate 함수는 1:1 치환이라고 했죠?

그래서, '1'은 '1'로 치환해주는데 문자열에서 1이 없으니까 무시되고 'a'는 공백과 매칭되니 'a'는 공백으로 치환되게 됩니다. 이는 바꿀 문자열을 공백으로 두지 않기 위한 하나의 방법이죠.

 

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

 

여기서 translate 함수에 자세히 들어가보면,

m(찾을 문자열의 수) = n(바꿀 문자열의 수) : 1 대 1 치환

m(찾을 문자열의 수) > n(바꿀 문자열의 수) : 매칭되지 않는 찾을 문자열은 삭제 (짝꿍이 없으므로)

m(찾을 문자열의 수) < n(바꿀 문자열의 수): 매칭되지 않는 바꿀 문자열은 무시(짝꿍이 없으므로)

 

select translate('abc', 'ab', '12'),  --a,b의 짝꿍이 각각 1,2로 있으므로 제대로 치환

       translate('abc','ab', '1'),  --b의 짝꿍이 없으므로 삭제

       translate('abc','a', '12')  -- 2의 짝꿍이 없으므로 무시됨

from dual;

 

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

 

translate 함수와 replace 함수의 차이점에는 특수문자 치환의 가능 여부가 있습니다. 

replace 함수로 흩어져있는 데이터를 삭제하는 기능은 힘듭니다. 왜냐하면, 단어 단위로 치환하기 때문에..

 

select replace('a!b#c$ba', '!#$', ''), translate('a!b#c$ba', '1!#$', '1')

from dual;

replace 함수로 해보니 왠걸.. 제거가 하나도 되지 않았죠? '!#$' 라는 단어가 문자열에 없기 때문입니다.

반면, translate 함수는 아주 잘 치환해주었네요.

 

이렇듯, translate 함수는 한번에 불필요한 문자를 제거할 때 유용합니다.

여러 시도로 비교해봅시다.

 

select replace('a!b#c$ba', '!#$', ''),

       translate('a!b#c$ba', '!#$', ''),

       translate('a!b#c$ba', '1!#$', '   '),

       translate('a!b#c$ba', '1!#$', '1')

from dual;

첫 번째 시도, replace 함수는 '!#$' 라는 단어가 문자열에 없기 때문에 치환 실패,

두 번째 시도, 바꿀 문자열에 공백을 넣었으므로, 데이터가 다 삭제..

세 번째 시도, 바꿀 문자열을 ' ' 띄어쓰기로 치환했으므로 저런 결과가 나오네요.

네 번째 시도, '1'은 '1'로 '!' '#' '$' 은 각각 공백으로 치환이 되었네요.

 

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

 

+ 1,

숫자 데이터가 1,000 라는 형식은 ,(컴마) 때문에 문자로 인식합니다.

'1,000'

 

그래서 ,(컴마)를 치환한다해도 이미 문자로 들어와서 '1000' 그대로 문자가 되어버리죠.

그 후 또 숫자로 변환시켜줘야 숫자 형식으로 사용할 수 있습니다.

select '1,000', replace('1,000', ',',''), translate('1,000', '1,','1')

from dual;

그러므로.. 숫자 타입으로 데이터를 사용하고 싶다면 ,(컴마)를 제거한 후 데이터를 읽어오는게 좋겠죠?

 

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

 

+ 2.

=> student 테이블에서 학생 이름의 두 번째 글자를 #으로 치환

select name, replace(name, substr(name,'2','1'), '#') AS 이름

from student;

 

 

=> student 테이블에서 jumin 컬럼의 주민등록번호 뒤 7자리를 #로 치환

이 방법에는 다양한 방법이 있지만, 

처음에는 다양한 표현식을 익혀놓고, 나중에는 속도가 더 빠른 방식을 선택하는게 좋다고 합니다.

함수를 많이 불러들일수록 속도가 느려지므로 함수의 사용은 성능 측면에서 고려해봐야 할 부분입니다.

 

select jumin,

       substr(jumin,'1','6') || '#######' AS "1",

       rpad(substr(jumin,'1','6'),13,'#') AS "2",

       replace(jumin, substr(jumin,'7'), '#######') AS "3",

      translate (jumin, substr(jumin,'7'), '#######') AS "4"

from student;

* 참고로 translate 함수를 사용하면 원치 않았던 앞의 문자들도 치환이 될 수 있다는..

 

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

 

오늘도 SQL과 길고 긴 여정이었네요.

제대로 정리가 된 건지 모르겠는데..

Ctrl + F 를 잘 활용해주세요!

 

 

참고: KIC 캠퍼스 머신러닝기반의 빅데이터분석 양성과정