[SQL 쿡북/06장] 문자열 작업

06장. 문자열 작업

---

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

• 각 DBMS가 해당 쿼리를 실행하는 방식은 다르므로, DBMS에서 제공하는 내장함수를 활용하는 것이 중요하다.
• 대부분의 DBMS는 MySQL의 GROUP_CONCAT 함수 또는 STRING_ADD함수와 같이 문자열의 연결에 특화하여 설계된 함수를 사용하면 쉽다.

💎 DB2

• LIST_AGG를 사용하여 구분된 목록을 작성한다.

SELECT deptno,
	   list_agg(ename, ',') within GROUP(Order by 0) as emps
  FROM emp
group by deptno

0. From emp → Group by deptno → SELECT deptno, list_agg(...) 해석
1. 

💎 MySQL

• 내장 함수 GROUP_CONCAT을 사용하여 구분된 목록을 작성한다.

SELECT deptno,
	   group_concat(ename, ',') within GROUP(Order by 0) as emps
  FROM emp
group by deptno

💎 Oracle

• 내장 함수 SYS_CONNECT_BY PATH를 사용하여 구분된 목록을 작성한다.

SELECT deptno,
	   litrim(sys_connect_by_path(ename, ','), ',') emps
  FROM (
SELECT deptno,
	   ename,
       row_number() over
       			(partition by deptno order by empno) rn,
       count(*) over
       			(partition by deptno) cnt
  FROM emp
  	   )
  WHERE level = cnt
  start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1

💎 PostgreSQL과 SQL Server

• STRING AGG함수는 GROUP_CONCAT과 유사하다.

SELECT deptno,
	   string_agg(ename order by empno separator, ',') as emps
   FROM emp
GROUP BY deptno

✔️ LIST_AGG는 현재 DB2에만 존재한다. 유사한 함수로 대체할 수 있으며 더 간단하다.

💎 MySQL

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

💎 PostgreSQL과 SQL Server

• STRING_AGG로 간단히 변경하면 사용 가능하다.

💎 Oracle

• Oracle 쿼리를 이해하는 첫 번째 단계는 분석이다.
• 인라인 뷰를 단독으로 실행하면 결과셋이 생성된다.

---

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

• 표면적으로 SQL이 구분된 문자열을 구분된 값의 목록으로 처리하는 작업을 해야 할 것처럼 보일 수 있지만 실제로는 그렇지 않다.
• 따옴표 안에 쉼표가 포함된 경우 SQL은 다중값 목록임을 알지 못한다.
  → 따옴표 사이 모든 것을 하나의 문자열 값으로 처리하려고 하므로,
    문자열을 개별로 나눠야한다.
• 핵심은 개별 문자가 아닌, 문자열로 이동하는 것이다.

💎 DB2

• IN 목록에 전달된 문자열을 하나씩 식별한 뒤, 행으로 변환할 수 있다.
• ROW_NUMBER, LOCATE, SUBSTR 함수가 유용하다.

💎 MySQL

• IN 목록에 전달된 문자열을 하나씩 짚어서 행으로 변환할 수 있다.

💎 Oracle

• IN 목록에 전달된 문자열을 쉽게 행으로 변환할 수 있다.
• ROWNUM, SUBSTR, INSTR함수가 유용하다.

💎 PostgreSQL

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

💎 SQL Server

• IN 목록에 전달된 문자열을 쉽게 행으로 변환할 수 있다.
• ROW_NUMBER, CHARINDEX 및 SUBSTRING함수가 유용하다.

✔️ 가장 중요한 것은, 문자열을 하나씩 나누는 것이다!
     DBMS에서 제공하는 함수를 사용해 문자열을 개발 숫자값으로 파싱하면 된다.

---

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

💎 DB2

• 문자열 행을 알파벳 순으로 정렬하기 위해선, 각 문자를 하나씩 짚어본 뒤 정렬한다.

💎 MySQL

• 핵심은 GROUP_CONCAT 함수이다.
  • GROUP_CONCAT을 사용해 각 이름을 구성하는 문자를 연결할 뿐만 아니라,
    순서도 지정할 수 있다.

💎 Oracle

• SYS_CONNECT_BY_PATH 함수를 사용해 반복하여 목록을 생성할 수 있다.

💎 PostgreSQL

• 문자열 내에서 문자를 정렬하기 위해 STRING_AGG를 추가한다.

💎 SQL Server

• 2017 이후 ver: STRING_AGG를 사용하는 PostgreSQL의 해법 사용
• 2017 이전 ver: 문자열 행을 하나씩 짚어본 뒤, 해당 문자를 정렬한다.

---

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

• REPLACE 및 TRANSLATE 함수는 문자열 및 개별 문자를 조작하는데 매우 유용하다.
• 핵심은 모든 숫자를 단일 문자로 변환하는 것이다.

💎 DB2

• TRANSLATE, REPLACE 및 POSSTR 함수를 사용해 각 행의 숫자를 분리한다.
• 고정 길이 CHAR로의 캐스팅에 따른 불필요한 공백 제거를 위해 REPLACE를 사용한다.

💎 MySQL

• TRANSLATE 함수를 지원하지 않으므로 각 행을 살펴보고 문자별로 평가해야 한다.

💎 Oracle

• TRANSLATE, REPLACE 및 INSTR 함수를 사용해 각 행의 숫자를 분리한다.
• REPLACE를 사용해 고정 길이 CHAR로의 캐스트로 발생한 불필요한 공백을 제거한다.
• 뷰 정의에서 명시적 유형 변환 호출을 유지하려면 VARCHAR2로 캐스트하는 것이 좋다.

💎 PostgreSQL

• TRANSLATE, REPLACE 및 STRPOS 함수를 사용해 각 행의 숫자를 분리한다.
• 뷰 정의에서 명시적 유형 변환 호출을 유지하기 위해서 VARCHAR로 캐스트하는 것이 좋다.

💎 SQL Server

• 와일드카드 검색과 함께 내장 함수 ISNUMERIC을 사용하면 숫자가 포함된 문자열을 쉽게 식별할 수 있다.
• 하지만 TRANSLATE를 지원하지 않으므로 문자열에서 숫자 문자를 가져오는 것은 효율적이지 않다.

✔️ 모든 숫자를 하나의 문자로 변환하는 것이 핵심이다.
     다음과 같이 한다면 다른 숫자를 검색하는 대신, 한 문자만 검색하면 된다.

---

🎨 6.14 n번째로 구분된 부분 문자열 추출하기

• 목록의 이름 순서를 유지하면서 각 이름을 개별 행으로 반환하는 것이다.

💎 DB2

• 뷰에서 반환한 NAME을 살펴본 뒤, ROW_NUMBER 함수를 사용해
  각 문자열에서 원하는 문자열을 남긴다.

💎 MySQL

• 뷰에서 반환한 NAME을 살펴본 뒤, 쉼표 위치를 사용해
  각 문자열에서 원하는 문자열을 남긴다.

💎 Oracle

• 뷰에서 반환한 NAME을 살펴본 뒤, SUBSTR과 INSTR을 사용해
  각 문자열에서 원하는 문자열을 남긴다.

💎 PostgreSQL

• SPILT_PART 함수를 사용해 개별 이름을 행으로 반환한다.

💎 SQL Server

• SQL Server에서 STRING_SPILT 함수는 전체 작업을 수행하지만
  단일 셀만 사용할 수 있다.
• CTE 내에서 STRING_AGG를 사용해 STRING_SPILT에 필요한 방식으로 데이터를 표시한다.

---

🎨 6.15 IP 주소 파싱하기

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

💎 DB2

• 재귀 WITH 절을 사용한 IP 주소의 반복과 SUBSTR을 사용해 처리한다.
• 모든 숫자 집합 앞에 마침표가 있고, 같은 방식으로 치리될 수 있도록
  IP 주소의 제일 앞에 마침표를 추가한다.

💎 MySQL

• SUBSTR_INDEX 함수를 사용해 IP 주소의 파싱을 진행한다.

💎 Oracle

• 내장 함수 SUBSTR 및 INSTR을 사용해 IP 주소를 구분해 파싱한다.

💎 PostgreSQL

• 내장 함수 SPILT_PART를 사용해 IP 주소를 파싱한다.

💎 SQL Server

• 재귀 WITH 절을 사용해 IP 주소를 통한 반복을 수행하고 SUBSTR을 사용해 파싱한다.
• IP 주소 앞에 마침표를 추가해 모든 숫자 모음 앞에 마침표를 넣고
  같은 방식으로 처리한다.

---

🎨 6.16 소리로 문자열 비교하기

🌎 철자의 오류가 있는 경우와, 오류는 아니지만 영국, 미국처럼 철자를 다르게 쓰는
     경우, 일치하는 두 단어가 서로 다른 문자열로 표횐되는 경우가 종종 있다.
     SQL은 단어의 소리 방식을 나타내는 방법을 제공한다.

• SOUNDEX 함수를 사용하여 문자열을 영어로 말할 때 들리는 방식으로 변환한다.
• 간단한 자체 JOIN을 통해 같은 열의 값을 비교할 수 있다.

💎 SOUNDEX?

• soundex는 미국 인구 조사에서 이름과 장소명에서의 서로 다른 맞춤법을
  해결하고자 개발한 알고리즘이다.
• soundex는 이름의 첫 글자를 유지한 다음, 나머지 값이 음성학적으로 비슷한 경우 같은 값을 갖는 숫자로 대체한다.
  • 예를 들어, m과 n은 모두 숫자 5로 대체된다.

---

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

• 이 문제의 해법의 여러 부분으로 구성된다.
  1. 고려할 텍스트의 범위를 설명하는 방법을 찾는다.
  2. 유효한 형식의 텍스트를 모두 제거한다.
  3. 여전히 보이는 텍스트가 있는지 확인한다.
     여기에 해당하면 형식이 잘못된 것을 뜻한다.

💎 추가 설명

• 이 해법의 핵심은 쿼리를 작성하는 사람이 요구하는 '명백한 텍스트'를 감지하는 것
• 더 합리적인 값 집합으로 필드 범위를 좁힐 방법이 필요하다.
  1. 패턴 A를 사용해 고려할 '확실한' 집합을 정의한다.
  2. '좋은' 기준에 대해 정의한다.
  3. 그 후 남아있는 텍스트는 정의상 잘못된 형식이어야 한다.