[SQL 쿡북] 6. 문자열 작업(18) p150

SQL은 복잡한 문자열 조작을 수행하도록 설계되지 않은 만큼, SQL에서 문자열로 작업하는 작업은 간혹 번거롭고 짜증 나는 일일 수 있습니다. SQL은 좋기도 하지만, 불편하기도 하며, 때로는 형편없습니다. 문자열을 작업할 때는 SQL에서 수행할 수 있는 작업과 수행할 수 없는 작업을 더 잘 이해할 수 있습니다. 대부분의 경우, 문자열 구문 파싱 및 변환이 얼마나 쉬운지를 알면 놀랄 뿐만 아니라, 특정 작업을 수행하는 데 필요한 SQL의 형태에도 놀랄 것입니다. 

 

이어지는 많은 레시피는 현재 이 책에서 다루는 모든 DBMS에서 사용할 수 있는 TRANSLATE 및 REPLACE 함수를 사용합니다. 다만 예외적으로 MySQL은 REPLACE만 쓸 수 있습니다. 마지막에 나오는 중첩된 REPLACE 함수로 TRANSLATE의 효과를 낼 수 있다는 점에도 주목해야 합니다. 

 

많은 경우 문자열을 한 번에 한 글자씩 이동하는 기능이 필요하지만, 불행히도 SQL은 이것을 쉽게 만들어내지 못합니다. SQL은 제한된 루프 기능을 활용하여 문자열을 이동하는 루프를 흉내 내야 합니다. 

 

6.1 문자열 짚어보기

데카르트 곱을 사용하여 문자열의 각 문자를 각 행에 반환하는 데 필요한 행 수를 생성합니다.

 

문자열의 문자를 반복하는 핵심은 필요한 반복 횟수를 생성하기에 충분한 행이 있는 테이블과 조인하는 것입니다.

인라인 뷰 E의 카디널리티는 1이고 인라인 뷰 ITER의 카디널리티는 10입니다. 그러면 데카르트 곱은 10행입니다. 이런 식으로 행을 생성하는 것은 SQL에서 루프를 모방하는 첫 번째 단계입니다. 

이 해법은 WHERE 절을 사용하여 4개의 행을 반환한 후 루프를 종료합니다. 결과셋을 이름의 글자 수와 같은 수의 행으로 제한하려면 WHERE에서 ITER.POS <=  LENGTH(E.ENAME)을 조건으로 지정합니다. 

 

이제 E.NAME의 각 문자에 대해 행이 하나씩 있으므로 ITER.POS를 SUBSTR에 대한 매개변수로 사용하여 문자열의 문자를 탐색할 수 있습니다. ITER.POS는 행마다 증가하므로 E.ENAME에서 연속 문자를 반환하도록 각 행을 만들 수 있습니다. 이것이 바로 해법 예제가 작동하는 방식입니다.

6장 레시피의 가장 일반적인 시나리오는 문자열의 각 문자에 대한 행을 생성하고자 전체 문자열을 하나씩 살펴보거나, 특정 문자 또는 구분 기호 수만큼 행을 생성하도록 문자열을 짚어보는 것입니다.

 

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

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

따옴표로 작업할 때 문자 없이 두 개의 따옴표로만 구성된 문자열 값은 NULL 입니다.

 

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

10,CLARK,MANAGER

문자열에 쉼표가 몇 개 있는지 확인

문자열의 쉼표 수를 결정하려면 문자열의 원래 길이에서 쉼표가 없는 문자열의 길이를 뺍니다. 각 DBMS는 문자열 길이를 가져오고 문자열에서 문자를 제거하는 함수를 제공합니다. 대부분의 경우 이러한 함수는 LENGTH 및 REPLACE입니다.

 

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

이러한 상황은 잘못된 형식의 숫자 데이터, 특히 통화 데이터를 처리할 때 쉼표를 사용하여 0을 구분하거나, 수량과 통화 표시가 열에 혼합되는 경우 발생할 수 있습니다. 그 외에도 데이터베이스의 데이터를 CSV 파일로 내보내려고 하지만, CSV파일에 액세스할 때 구분기호로 읽히는 쉼표가 포함된 텍스트 필드가 있는 경우 입니다.

 

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

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

관심 있는 열에 숫자와 문자 이외의 문자가 포함되지 않을 때만 테이블에서 행을 반환하려고 합니다.

create view V as
select ename as data
  from emp
 where deptno=10
 union all
select concat(ename,', $',sal,'.00') as data
  from emp
 where deptno=20
 union all
select concat(ename,deptno) as data
  from emp
 where deptno=30
--------------------------------------------------------------------
 select data
   from V
  where data regexp '[^0-9a-zA-Z]' = 0

 

문자와 숫자 이외의 데이터가 포함된 행을 생략하여 반환.

숫자와 문자 이외의 것을 반환한 행은 거짓이라는 조건을 나타낸다.

 

문자열에서 찾을 수 있는, 모든 영숫자가 아닌 문자를 검색하여 문제를 해결하는 방법이 처음에는 직관적으로 보일 수 있지만, 반대로 수행하기가 더 쉽다는 것을 알 수 있습니다. 모든 영숫자 문자를 찾아내면 이들을 하나의 단일 문자로 변환하여 하나로 처리할 수 있습니다. 이 작업을 수행하려는 이유는 영숫자 문자를 전체적으로 함께 조작할 수 있기 때문입니다. 모든 영숫자 문자를 지정한 단일 문자로 표시하는 문자열의 복사본을 생성하면, 영숫자 문자를 다른 문자와 쉽게 분리할 수 있습니다.

 

translate를 사용하는 다른 DB들과는 달리 MySQL에서는 정규표현식을 통해 쉽게 나타낼 수 있었다.

 

6.7 이름에서 이니셜 추출하기

Stewie Griffin --> S.G

SQL에서 이름 형식을 처리하는 일반적인 해법을 만들기란 쉬운 일이 아닙니다. 

translate함수가 없는 MySQL은 다른 해법에 비해 쿼리의 길이가 3배이상 길어지는 경향을 보인다.

select case
           when cnt = 2 then
             trim(trailing '.' from
                  concat_ws('.',
                   substr(substring_index(name,' ',1),1,1),
                   substr(name,
                          length(substring_index(name,' ',1))+2,1),
                   substr(substring_index(name,' ',-1),1,1),
                   '.'))
          else
            trim(trailing '.' from
                 concat_ws('.',
                  substr(substring_index(name,' ',1),1,1),
                  substr(substring_index(name,' ',-1),1,1)
                  ))
          end as initials
   from (
 select name,length(name)-length(replace(name,' ','')) as cnt
   from (
 select replace('Stewie Griffin','.','') as name from t1
        )y
        )x

인라인 뷰 Y는 이름에서 마침표를 제거하는 데 사용합니다.

인라인 뷰 X는 이름에서 찾은 공백수만큼 SUBSTR 함수를 호출하여 이니셜을 추출할 수 있습니다. SUBSTRING_INDEX에 대한 한 번의 호출은 공백을 기준으로 문자열을 개별 이름으로 구문 분석합니다. 성과 이름만 있으므로 case 문의 ELSE 부분에 있는 코드가 실행됩니다.

6.8 문자열 일부를 정렬하기

데이터의 끝자리 2문자를 이용해 정렬하려고 한다.

6.9 문자열의 숫자로 정렬하기

이 책의 집필 시점에 TRANSLATE 함수 제공X -- 현재도 없음

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

SQL에서 구분된 목록을 만들 수 있다는 것은 일반적인 요구 사항이므로 유용합니다. SQL:2016 표준은 이 작업을 수행하기 위해  LIST_AGG를 추가했지만, 현재 DB2에만 이 함수가 존재합니다. 다행히 다른 DBMS도 유사한 함수로 대체할 수 있으며 구문이 더 간단합니다.

 

MySQL의 GROUP_CONCAT 함수는 전달된 열에 있는 값을 연결합니다. 집계 함수이므로 쿼리에 GROUP BY가 필요합니다.

 

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

7654,7698,7782

따옴표 안에 쉼표가 포함된 경우 SQL은 다중값 목록임을 알지 못합니다. SQL은 따옴표 사이의 모든 것을 하나의 문자열 값으로, 즉 단일 엔티티로 처리하려 하므로, 문자열을 개별 EMPNO로 나누어야 합니다. 이 해법의 핵심은 개별 문자가 아닌, 문자열로 이동하는 것입니다. 

 

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

향상된 표준화를 통해 더 유사한 것을 허용하는, 활용도가 높은 해법의 좋은 사례

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

6.14 n번째로 구분된 부분 문자열 추출하기 -mysql 문법 오류

6.15 IP 주소 파싱하기

111.22.3.4

DBMS에서 제공하는 내장 함수에 따라 해법이 각기 다릅니다. 여기서의 핵심은 DBMS와 무관하게 마침표와 마침표가 둘러싼 숫자를 찾는 것입니다.

 

SUBSTRING_INDEX(문자열, 구분자, 구분자 Index)

6.16 소리로 문자열 비교하기

사운덱스는 미국 인구 조사에서 이름과 장소명에서의 서로 다른 맞춤법을 해결하고자 개발한 알고리즘으로, 데이터베이스나 컴퓨팅보다 훨씬 앞선 개념입니다.

 

6.17 패턴과 일치하지 않는 텍스트 찾기

 

 

6.18 마치며

문자열 일치는 까다로운 작업이 될 수 있습니다. SQL은 편의를 위해 다양한 도구를 추가했으며 이를 마스터하면 문제를 해결할 수 있습니다. SQL 고유의 문자열 함수로 많은 작업을 수행할 수 있지만, 점점 더 많이 쓰이고 있는 정규표현식 함수를 사용하면 완전히 다른 수준으로 올라갑니다.

https://github.com/devcys22/SQLCookbook/blob/main/ch6%EB%AC%B8%EC%9E%90%EC%97%B4%EC%9E%91%EC%97%85.sql

GitHub - devcys22/SQLCookbook: SQL쿡북을 학습합니다.