[SQL] SQL Cookbook - 06. 문자열 작업

1. 문자열 짚어보기

- 문자열에서 각 문자를 행으로 반환하기 위해  문자열의 길이만큼 반복하며 출력 

DATALENGTH:

주어진 값의 길이를 byte단위로 반환한다. (문자열, 이진 데이터, 변수 등의 길이 측정) 

SUBSTRING:

문자열에서 특정 부분을 추출한다. ex> SUBSTRING(문자열, 시작위치, 개수)

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	// 1~10까지의 id값을 가지는 t10테이블
where iter.pos <= length(e.ename)

 

2. 문자열에 따옴표 포함하기

- 주어진 문자열에 항상 짝수의 따옴표가 있어야 하며, 작은 따옴표를 문자열에 포함하려면 두 개의 따옴표를 사용해야 한다.

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

 

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

'10,CLARK,MANAGER' 해당 문자열에서 쉼표가 몇개 있는지 확인

- 문자열의 원래 길이에서 쉼표가 없는 문자열의 길이를 빼면 쉼표의 갯수를 알 수 있다.

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

 

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

TRANSLATE:

특정 문자(집합)를 다른 문자(집합)로 변환. ex> TRANSLATE('Hello', 'H', 'X') => Xello

REPLACE:

문자열에서 특정 문자(문자열)를 다른 문자(문자열)로 대체하는데 사용됨. ex> REPLACE('Hello, World!', 'World', 'Universe') => Hello Universe!

select ename,
  repleace(translate(ename, 'aaaaa', 'AEIOU'),'a','') as stripped1,
  sal,
  replace(cast(sal as char(4)),'0','') as stripped2
from emp

 

5. 숫자 및 문자 데이터 분리하기

4번과 같이 Translate와 replace함수를 사용하여 숫자를 문자 데이터에서 구분하여 분리한다.

select replace(
   translate(data, '0123456789','0000000000'),'0','') as ename,
      cast(
      replace(
      translate(lower(data),
            'abcdefghijklmnopqrstuvwxyz',
            rpad('z',26,'z')),'z','') as integer) as sal
   from(
select ename || sal as data
   from emp
       ) X

 

 

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

Translate 함수를 사용하여 모든 영숫자를 단일 문자로 변환한다. 그런 다음 변환된 영숫자 문자 이외의 문자가 있는 행을 식별한다.

TRANSLATE:

 문자열에서 특정 문자를 다른 문자로 변환

LOW:

 문자열을 소문자로 변환

RPAD:

 주어진 길이만큼 문자열의 오른쪽에 공백(padding)또는 문자 추가

select data
   from V
 where translate(lower(data),
                 '0123456789abcdefghijklmnopqrstvwxyz',
                 rpad('a',36,'a')) = rpad('a',length(data),'a')

 

7. 이름에서 이니셜 추출하기

내장함수 Replace, Translate, rpad를 사용하여 이니셜을 추출한다.

select replace(
       replace(
       translate(replace('Stewie Griffin', '.', ''),
               'abcdefghijklmnopqrstvwxyz',
               rpad('#',26,'#') ), '#', '' ),' ', '.' ) || '.'
from t1

 

8. 문자열 일부를 정렬하기

문자열의 특정 부분을 기준으로 정렬하려면 Length 및 Substr을 조합하여 사용한다.

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

 

9. 문자열의 숫자로 정렬하기

내장 함수 Replace 및 Translate를 사용하여 문자열의 숫자로 정렬한다.

select data
  from V
order by 
  cast(
   replace(
  translate(data,
   replace(
  translate(data, '0123456789','##########'),
           '#'), rpad('#',20,'#')), '#'))

 

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

STRING_AGG: 

 특정 열의 값을 지정된 구분 기호로 결합하여 하나의 문자열로 반환한다.

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

 

11. 구분된 데이터를 다중값 IN 목록으로 변환하기

IN 목록에 전달된 문자열을 쉽게 행으로 변환할 수 있다. Split_Part 함수를 사용하면 문자열을 개별 숫자로 쉽게 변환할 수 있다.

SPLIT_PART:

 지정된 구분자를 기준으로 문자열을 분할하고 해당 위치의 부분 문자열을 반환한다.

select ename, sal, deptno
  from emp 
 where empno in (
select cast(empno as integer) as empno
  from (
select split_part(list.vals, ',', iter.pos) as empno
  from (select id as pos from t10) iter,
       (select ',' || '7654,7698,7782,7788' || ',' as vals
         from t1) list
 where iter.pos <=
      length(list.vals) - length(replace(list.vals, ',', ''))
      ) z
 where length(empno) > 0
      )

 

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

문자열 내에서 문자를 정렬하기 위해 String_Agg함수를 사용한다.

select ename, string_agg(c, '' ORDER BY c)
from (
  select a.ename, substr(a.ename, iter.pos, 1) as c
  from emp a,
       (select id as pos from t10) iter
  where iter.pos <= length(a.ename)
  order by 1, 2
       ) x
Group By c

 

13. 숫자로 취급할 수 있는 문자열 식별하기

Translate, Replace, Strpos 함수를 사용하여 각 행의 숫자를 분리한다. 

Replace 함수를 사용하여 고정 길이 Char 로의 캐스트로 발생한 불필요한 공백을 제거한다.

STRPOS:

문자열에서 지정된 부분 문자열의 첫 번째 등장 위치(인덱스)를 반환한다.

select cast(
  case 
  when
   replace(translate(mixed, '0123456789', '9999999999'), '9', '')
   is not null
  then
    replace(
  translate(mixed,
    replace
  translate(mixed, '0123456789', '9999999999'), '9', ''),
     rpad('#', length(mixed), '#')), '#', '')
  else 
    mixed
  end 
    as integer) as mixed
from V
where strpos(translate(mixed, '0123456789', '9999999999'), '9') > 0

 

14. n번째로 구분된 부분 문자열 추출하기

Split_Part 함수를 사용하여 개발 이름을 행으로 반환한다.

select name
from (
  select iter.pos, split_part(src.name, ',', iter.pos) as name
  from (select id as pos from t10) iter,
       (select cast(name as text) as name from v) src
  where iter.pos <=
       length(src.name) - length(replace(src.name, ',', '')) +1
     ) x
where pos = 2

 

15. IP 주소 파싱하기

내장 함수 Split_Part를 사용하여 IP 주소를 파싱한다.

select split_part(y.ip, '.', 1) as a,
       split_part(y.ip, '.', 2) as b,
       split_part(y.ip, '.', 3) as c,
       split_part(y.ip, '.', 4) as d,
from  (select cast('92.111.0.2' as text) as ip from t1) as y

 

16. 소리로 문자열 비교하기

Soundex 함수를 사용하여 문자열을 영어로 말할 때 들리는 방식으로 변환한다. 

간단한 자체 조인을 통해 같은 열의 값을 비교할 수 있다.

SOUNDEX: 

 문자열의 발음을 나타내는 코드를 생성하는 SQL 함수로, 비슷한 발음을 가진 단어를 찾는 데 사용된다.

select an1.a_name as name1, an2.a_name as name2,
       SOUNDEX(an1.a_name) as Soundex_Name
from author_names an1
join author_names an2
  on (SOUNDEX(an1.a_name) = SOUNDEX(an2.a_name)
  and an1.a_name not like an2.a_name)

 

17. 패턴과 일치하지 않는 텍스트 찾기 (feat. 정규 표현식)

전화번호처럼 일부 구조화된 텍스트값이 포함된 필드가 있을 때 해당 값의 구조화가 잘못 된 항목을 찾는다.

1) 고려할 전화번호의 범위를 설명하는 방법을 찾는다.

2) 유효한 형식의 전화번호를 모두 제거한다.

3. 여전히 보이는 전화번호가 있는지 확인한다. 여기에 해당하면 형식이 잘못된 것임을 알 수 있다.

select emp_id, text
from employee_comment
where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
  and regexp_like(regexp__replace(text,
                                  '[0-9]{3}[-. ][0-9]{3}\1[-. ][0-9]{4}', '***'),
                                  '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')

 

 

 

 

*본문의 Query문은 PostgreSQL을 기준으로 작성하였습니다.