[Sql] 문자 함수

문자 함수

1-1. INITCAP, LOWER, UPPER 함수

 select ENAME,    // 원본 데이터 그래도 출력
        initcap(ENAME),  // 첫 문자만 대문자 치환
        lower(ENAME),   // 전체 소문자 치환
        upper(ENAME)   // 전체 대문자 치환
 from EMP;

1-2. SUBSTR 함수

substr 함수는 문자열의 일부 데이터만 필요한 경우 사용됩니다. 예를 들어보면 주민등록번호에서 성별 추출 등이 있습니다.

 

student 테이블의 name 컬럼에서 성만 출력

//name 컬럼의 1 번째 위치에서 1 개의 문자를 추출하겠다는 의미
select name, substr(name, 1, 1) as 성
from student;

 1-3. INSTR 함수

문자열 str 에서 substr 이 가리키는 위치를 반환하고, 일치하는 정보가 없다면, 0 을 반환합니다.

SELECT INSTR('mysql', 'sql'); //3

 

02)234-5678
031)548-7788
032)447-6456

에서 지역번호 추출

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) AS 지역번호2
 from student;

1-4. LPAD, RPAD 함수

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

select 'abcde',
        lpad('abcde',6,'*'),  
        lpad('abcde',4,'*')  
from dual;

문자열의 자릿수('abcde'->5)가 lpad의 자릿수(4) 보다 작은 경우 데이터 손실이 일어날 수 있는데

만약 무조건 * 4개를 삽입 하고 싶으면 다음과 같이 length()를 사용하면 된다.

select id, lpad(id, length(id)+4, '*')
from student;

 1-5. TRIM 함수

다음과 같이 공백을 제거하는데 사용됩니다.

select 'abcabc', trim('abcabc'), trim(' abc ')
from dual;

1-6. REPLACE 함수

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

select replace('abcba', 'a', 'A'), replace('abcba', 'ab', 'AB')
from dual;

 

 

 

sql 문제

문제 1. student 테이블에서 학생 이름의 두 번째 글자를 #으로 치환

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

문제 2. 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",
from student;

문제 3.

1. 문자열은 앞에 위치하고, 숫자는 뒤에 존재한다.
2. 숫자의 경우 0~4글자인데, 4글자가 안되는 것이 있다면 앞에 0을 채워 숫자가 4개가 되도록 해라.

ex1) abcde123 의 경우는 abcde0123 으로 변환되어야 한다.
ex2) dfadfjljfljdlfaj9 의 경우 dfadfjljfljdlfaj0009이 되어야 한다.
ex3) abaa10 -> abaa0010

select concat (regexp_replace('abcde0', '[0-9]', ''), lpad(regexp_replace('abcde0', '[a-z]', ''),4, '0'));