[SQL 쿡북] Chapter 6 : 문자열 작업 (MYSQL)

6.1 문자열 짚어보기 

id 값 1~10을 가지는 피벗테이블 t10을 이용하여 ename인 'KING'을 한글자 씩 4개의 행으로 표시하려고 한다. 

 

select ename, iter.pos
    from (select ename from emp where ename = 'KING') e,
         (select id as pos from t10) iter
  where iter.pos <= length(e.ename)

KING이름을 가진 사람은 한 명이고 4글자 이므로 위와 같은 결과 셋을 나타나게 되는데, 이를 이용해서 문제를 해결할 수 있다.

select substr(e.ename, iter.pos, 1) as C
    from (select ename from emp where ename = 'KING') e,
         (select id as pos from t10) iter
  where iter.pos <= length(e.ename)

6.2 문자열에 따옴표 포함하기 

문자열에 따옴표를 포함하려고 한다. 

select 'g''day mate' qmarks from t1 union all
select 'beabers'' teeth' from t1 union all
select '''' from t1

여는 괄호가 있으면 닫는 괄호가 있듯이 따옴표도 마찬가지다. 그 안에 짝수의 따옴표가 있어야 하고, 하나의 작은따옴표는 두 개의 따옴표를 사용한다. 

6.3 문자열에서 특정 문자의 발생 횟수 계산하기

10,CLARK,MANAGER 에 쉼표가 몇개 있는지 확인하려고 한다. 

select (length('10,CLARK,MANAGER') - 
	   length(replace('10,CLARK,MANAGER',',','')))/length(',')
       as cnt
from t1

(전체 문자열 길이 - 쉼표를 없앤 문자열의 길이) / 쉼표의 길이

를 이용하여 쉼표가 몇개인지 구할 수 있다.

6.4 문자열에서 원하지 않는 문자 제거하기 

select ename, sal
	from emp

이 결과셋에서 ename은 모음을 제거하고, sal은 모든 0을 제거하여 표시하려고 한다. 

select ename,
	replace(
    replace(
    replace(
    replace(
    replace(ename, 'A',''),'E',''),'I',''),'O',''),'U','')
    as tripped1,
    sal,
    replace(sal,0,'') stripped2
from emp

6.5 숫자 및 문자 데이터 분리하기 

MYSQL 생략

6.6 문자열의 영숫자 여부 확인하기

select data
 from V
 where data regexp '[^0-9a-zA-Z]' = 0

데이터에 문자와 숫자 이외의 데이터가 포함된 행을 생략하여 반환하려 할 때 사용한다. 0-9a-zA-Z 는 가능한 모든 숫자와 문자를 나타내고 ^기호는 부정을 의미하므로 '숫자와 문자 이외의 것을 반환한 행은 거짓'임을 의미한다. 

6.8 문자열 일부를 정렬하기

마지막 두 문자를 기준으로 이름 레코드를 정렬하려고 한다.

select ename
	from emp
 order by substr(ename, length(ename)-1,2)

6.10 테이블 행으로 구분된 목록 만들기

select deptno, ename emps
	from emp
order by deptno

위의 결과 셋을 동일한 deptno를 가지는 직원들을 쉼표로 구분된 값으로 반환하려고 한다.

select deptno,
	group_concat(ename order by empno separator ',') as emps
  from emp
group by deptno

6.12 문자열을 알파벳 순서로 정렬하기

select ename,
	group_concat(c order by c separator '') 
  from (
select ename, substr(a.ename, iter.pos, 1) c
  from emp a,
	   (select id pos from t10) iter
 where iter.pos <= length(a.ename)
	   ) x
group by ename

위 6.1번을 응용한 것이다. 이름 한 글자를 하나의 행으로 반환하고, GROUP_CONCAT함수를 수행하여 알파벳 순으로 각 문자를 연결한 것이다.