함수에 대해 알아보자 3편 (feat. 검색, 변환, 대체)

안녕하세요, 끙정입니다.

 

오늘은 문자열 함수의 활용 방법인 검색, 변환, 대체에 대해서 알아보겠습니다.

 

1. 검색

검색은 문자열에서 특정 문자열이 있는지,

있다면 어디쯤에 있는지를 조사합니다.

1.1 INSTR

기본 검색 함수는 INSTR 이며 인수로 (문자열, 패턴, 시작 위치, 순서)를 전달하되,

시작 위치와 순서는 생략시 모두 1을 적용합니다.

문자열이 있으면 발견 위치를 리턴하며 없으면 0을 리턴합니다.

 

INSTR(s, p, b, n) /* Oracle */
CHARINDEX(p, s, b) /* SQL Server */
POSITION(p IN s) /* MariaDB */
/* s에서 n번째 p를 b부터 찾아줘 */

 

/* Oracle */
SELECT INSTR('우리나라 대한민국', '나라') FROM DUAL;
SELECT INSTR('우리나라 대한민국', '민족') FROM DUAL;

/* SQL Server */
SELECT CHARINDEX('나라', '우리나라 대한민국');
SELECT CHARINDEX('민족', '우리나라 대한민국'); 

/* MariaDB */
SELECT POSITION('나라' IN '우리나라 대한민국');
SELECT POSITION('민족' IN '우리나라 대한민국');

 

 

SELECT INSTR('국민에 의한 국민을 위한 국민의 국민', '국민', 3) FROM DUAL;
SELECT INSTR('국민에 의한 국민을 위한 국민의 국민', '국민', 1, 3) FROM DUAL;
SELECT INSTR('국민에 의한 국민을 위한 국민의 국민', '국민', -1) FROM DUAL;

 

 

첫 번째 쿼리는 3번째 문자부터 '국민'을 찾기 때문에 8번째 문자 자리를 반환합니다.

두 번째 쿼리는 1번째 문자부터 3번째 등장하는 '국민'을 찾기 때문에 15번째 문자 자리를 반환합니다.

-1은 역순으로 찾습니다. 그렇기에 세 번째 쿼리는 뒤에서부터 '국민'을 찾아 19번째 문자 자리를 반환합니다.

 

1.2 SUBSTR

INSTR이 위치를 반환한다면,

SUBSTR은 함수에 해당하는 문자를 반환합니다.

 

SUBSTR(s, 1, n) /* Oracle */
LEFT(s, n) / RIGHT (s, n) /* SQL Server, MariaDB */
/* s문자열의 1번째 문자부터 n번째 문자까지 반환 */

 

SELECT SUBSTR('아름다운 대한민국 금수강산', 6, 4) FROM DUAL;
SELECT SUBSTR('아름다운 대한민국 금수강산', -4, 2) FROM DUAL;

 

 

첫 번째 쿼리는 6번째 문자부터 시작하여 4개의 문자를 출력합니다.

두 번째 쿼리는 -4 즉, 뒤에서 4번째 문자로부터 2개의 문자를 출력합니다.

 

부분 추출한 문자열을 검색이나 그룹핑 조건으로 사용할 수도 있습니다.

 

SELECT SUBSTR(name, 1, 1), COUNT(*) FROM tStaff GROUP BY SUBSTR(name, 1, 1)
ORDER BY COUNT(*) DESC;

 

 

2. 변환

2.1 LOWER, UPPER, INITCAP

LOWER, UPPER 함수는 대소문자를 변환합니다.

문자열을 전부 소문자나 대문자로 변환하며 INITCAP 함수는 단어의 첫 글자만 대문자로 변환합니다.

 

SELECT LOWER('wonderful SQL') FROM DUAL;
SELECT UPPER('wonderful SQL') FROM DUAL;
SELECT INITCAP('wonderful SQL') FROM DUAL; /* Oracle Only */

 

 

영문을 비교할 때 대소문자 변환 함수는 유용합니다.

해당 필드의 값이 y인지 Y인지 알 수 없을 때,

아래와 같이 변환 함수를 사용하면 좋습니다.

(SQL Server와 MariaDB는 대소문자를 구분하지 않습니다. 그러나 오라클은 구분합니다.)

 

SELECT * FROM tCity WHERE UPPER(metro) = 'Y';

 

실제 값은 y지만 잘 불러와진다.

 

2.2 TRIM, LTRIM, RTRIM

TRIM함수는 문자열에 불필요하게 남아 있는 앞뒤의 공백을 모두 제거합니다.

왼쪽, 오른쪽의 공백만 제거하려면 LTRIM, RTRIM을 사용합니다.

CHAR 타입은 선언한 길이만큼 뒤쪽에 공백이 있는데 단독으로 출력할 때는 별 문제 없지만,

다른 문자열과 연결할 때는 문제가 됩니다.

 

SELECT CONCAT(name, '사원님') FROM tStaff;
/* TRIM을 사용 */
SELECT CONCAT(TRIM(name), '사원님') FROM tStaff;

 

공백이 사라졌다-!

 

실제로는 CHAR보다 VARCHAR를 사용하기 때문에 이런 쿼리를 사용할 경우가 드물긴 합니다.

 

3. 대체

REPLACE 함수는 전체 문자열에서 패턴을 찾아 다른 문자열로 변경합니다.

 

SELECT REPLACE('저는 오라클을 씁니다.', '오라클', 'MariaDB') FROM DUAL;

 

 

패턴이 여러개면 모두 다 대체합니다.

특정 문자열을 빈 문자열로 대체하기도 합니다.

 

SELECT REPLACE('저는 오라클을 씁니다. 친구도 오라클을 씁니다.', '오라클', 'SQL Server') FROM DUAL;
                
SELECT REPLACE('GET_TOTAL_SCORE_', '_', ' ') FROM DUAL;

 

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24