Chapter6. 문자열 작업

문자 나누기

• sql의 단점 중 하나는 문자열 처리가 용이하지 않다는 점이다. 그렇기 때문에 여러 방법을 통해 문자열을 작업해야하는 경우가 있다.
• 먼저 하나의 단어를 인덱스별로 출력을 해야하는 경우 위 코드를 사용할 수 있다.

substr(<원본문자>, <가져올 위치>, <가져올 길이>)

• substr함수는 3개의 매소드를 통해 사용할 수 있는데, 문자/위치/길이 등으로 구분된다.
• 위 코드의 경우 ename이 KING이 데이터에서 KING 데이터를 뽑아서 하나의 인덱스 한 문자씩 출력했다.
  
• where조건문의 경우 문자 길이는 출력하는 숫자가 단어의 길이보다 더 길 경우 멈출 수 있도록 만들어 데카르트 제곱 출력이 되지 않도록 방지했다.
  
• 이렇게 where절을 쓰지 않을 경우 출력하는 숫자 1~10까지 총 10개의 인덱스가 출력되기에 빈값이 생기기 때문이다.
  
• substr 함수를 사용해서 다양한 형태로 하나의 단어를 출력할 수도 있다.
• a 데이터의 경우 1->2->3->4 위치의 문자를 출력하는 형태이고,
• b 데이터의 경우 4->3->2->1 위치의 문자를 출력하는 형태이기 때문에 뒤에서부터 데이터가 출력된다.

'(따옴표)문자 사용하기

• 문자열을 출력할 때 ' 를 넣어야 하는 경우 ' ' 사이에 ''두개를 넣어서 출력할 수도 있다.

특정 문자의 발생횟수

• 10, CLARK, MANAGER에서 따옴표가 나타난 횟수를 구하기 위한 코드이다.
• 원리는 원래 문자열 길이에서 원하는 문자를 뺀 문자열 길이를 빼주는 것이다.
  
• 중요한 점은 횟수를 찾으려는 문자열을 나눠줘야 정확한 count를 알 수 있다.

특정 문자 지우기

• 위 문자에서 이름은 영어의 모음을 지우고, 월급에서는 0을 지워보자
  
• mysql의 경우 translate함수가 없기 때문에 다중replace를 사용해야한다.
• replace마다 지우려는 문자열을 빈값으로 바꿔줘서 다중replace로 지울 수 있으며, 월급의 경우 0을 빈값으로 만들면 된다.

영숫자 확인하기

• 먼저 이름과 월급 및 단위, 부서 번호가 기록된 view를 만들고,
• 해당 view에서 일부 원하는 데이터만 뽑으려고 코드를 작성했다.
  
• mysql에서는 regexp 함수를 사용하면 정규식 표현을 쓸 수 있는데, 구체적이고 다양한 조건으로 문자열을 뽑을 때 유용하게 사용할 수 있다.

일부 문자로 정렬

• 해당 이름 데이터에서 이름 뒷부분을 기준으로 정렬을 할 때
  
• order by절에 substr 함수를 사용하면 맨 뒤 첫번 째 문자를 기준으로 데이터를 정렬할 수 있다.
• 여기서 중요한 점은 order by절에 함수 기능을 사용할 수 있다는 것이다.

테이블 행을 기준으로 데이터 구분

• 부서번호와 이름이 적힌 테이블 데이터를 부서 번호 별로 이름을 정렬을 해보자
  
• mysql에서는 group_concat함수가 있기에 편하게 할 수 있다.
  먼저 메인 select절에 group by로 묶을 컬럼을 넣어주고 group_concat에는 자료 출력에 묶을 컬럼을 넣어주면 된다.
  
• 안에 group_concat함수 안에 정렬과 구분자를 설정할 수 있다.

다중값 데이터를 IN으로 구분

• where조건절에 in을 통해 조건을 설정하는데 위와 같이 하나의 형태로 저장된 자료를 사용할 경우 제대로 sql에서 인식을 못한다.
  
• 그래서 위와 같이 substring_index를 사용하는데

  select empno, ename, sal, deptno
    from emp
   where empno in
         (
  select substring_index(
         substring_index(list.vals,',',iter.pos),',',-1) empno
    from (select id pos from t10) as iter,
         (select '7654,7698,7782,7788' as vals
            from t1) list
   where iter.pos <=
        (length(list.vals)-length(replace(list.vals,',','')))+1
        )

• 위 코드와 같이 where in절에 서브쿼리를 사용하는 것이다.
• 코드만 보고 이해하기 힘드니까 분석을 해보면
  
• 일단 서브쿼리 select절에서 뽑은 데이터는 위 10개의 데이터이다.
• 하지만 우리가 필요한 것은 총 4개의 숫자이기 때문에
  
• 서브쿼리 안 where조건절에 pos를 출력하고 4개의 문자 제한을 위해 4개 이하일 때만 출력할 수 있도록 조건을 추가했다.

문자열 알파벳순으로 정리하기

• 위와 같이 하나의 string형태의 자료를 알파벳순으로 정리할 수 있는 기능이 있다.
• 먼저 group_concat기능을 사용해야하는데 from 안 인라인 뷰 절이 중요하다.
  
• substr 기능을 통해 이름에서 순서대로 한글자씩 c 컬럼에 가져오는데, where조건절에 글자수만큼만 가져올 수 있도록 조건을 건다.
  
• 조건을 설정하지 않을 경우 이렇게 모든 iter.pos 숫자 10개를 다 출력한다. 그렇기에 where조건절에 조건을 둠으로써 필요한 데이터만 출력한다.
• 이제 메인 select절에 group_concat과 group 사용해서 group에 ename을 설정해서 이름별 group_concat(한글자씩 분리하고 알파벳 순으로 정렬)을 출력하면 하나의 문자열을 알파벳으로 정렬할 수 있다.

문자와 숫자 구분

• 먼저 숫자와 문자 섞인 하나의 view만들어준다. 해당 view는 문자와 숫자 섞여있다.
  
• 문자와 숫자 섞인 총 14개의 레코드 뷰가 만들어졌다. 중간에 숫자만 있거나, 숫자와 문자가 섞여있거나 문자만 있는 경우가 있다.
  
• 해당 코드를 통해 숫자만 출력할 수 있다.
• 일단 cast기능을 사용하는 것이 중요한데, cast는 해당 데이터의 type변환해주는 기능이다. 밑에 하나씩 분해한 코드를 보고 이해해보자
  
• mixed는 원래 기존의 view의 데이터이고, pos 순서, c는 mixed로부터하나씩 출력한 값이다.
• 여기서 제일 중요한 포인트는 where절에

ascii(substr(v.mixed, iter.pos, 1)) between 47 and 57

• 부분인데, ascii는 해당 값을 아스키코드 값으로 변환해준다.
• 그렇기 때문에 v.mixed로부터 뽑은 값 중에서 정수에 해당하는 아스키코드값 47~57이 아니면 출력되지 않도록 설정한 것이다.
• 이제 c 컬럼에는 mixed로부터 숫자만 해당하는 값이 출력된다.
• 그리고 메인 select절에 group은 mixed(원래 view데이터의 하나의 문자)별로 정수만 뽑은 c컬럼을 group_concat하고 cast기능을 통해 unsigned를 정수 값으로 변환해서 출력하면 하나의 문자당 숫자와 해당하는 값을 출력할 수 있다.

n번째 문자 출력하기

• 해당 데이터는 하나의 레코드에 여러 string이 섞여있다.
• 만약 내가 원하는 순서의 데이터만 뽑고 싶을 떄 사용할 수 있는 코드가 있다.
  
• 여기서 가장 중요한 포인트는 substring_index를 다중으로 사용한 것인데, 밑에 사진을 보면 이해하기 쉽다.
  
• 문자의 separator를 기준으로 하나씩 뽑은 다음 iter.pos(순서 데이터)를 통해 오름차순으로 데이터를 뽑은 substring_index에 다시 마지막 index만 뽑는 substring_index를 사용하면 위와 같이 여러 문자열이 섞인 데이터에서 separator를 기준으로 하나씩 뽑을 수 있다.
• 여기서 내가 뽑길 원하는 pos값 즉 순서를 메인 where절에 입력하면 원하는 n번째 데이터를 뽑을 수 있다.
• 인라인 뷰절에

where iter.pos <= length(scr.name) - (length(replace(scr.name, ",",""))-1)

• 위와 같은 where절을 사용하면 separator를 기준으로 문자 개수만큼만 출력하기 때문에 위에 10번까지 출력하지 않아도 된다.

IP파싱하기

• substring_index를 다중으로 사용해서 위 코드와 같이 '.'을 기준으로 ip를 구분해서 출력할 수도 있다.
• chapter.6(문자) 끝!