단일행 함수(SINGLE GROUP FUNCTION) SQL 문자열 처리함수LENGTH, LENGTHB, SUBSTR, UP-PER, LOWER, INSTR.....

종류

• 문자열 처리 함수
• 숫자 처리 함수
• 날짜 함수
• 형 변화 함수
• NULL 처리 함수

 

문자 처리 함수

LENGTH, LENGTHB, SUBSTR, UP-PER, LOWER, INSTR.....

LENGTH(컬럼명 | '문자열 값') : 글자의 갯수를 리턴

SELECT * FROM DUAL;

-- 더미 테이블 반환

SELECT LENGTH('HELLO') FROM DUAL;

--'문자열의 길이 : 5'

SELECT EMP_NAME, LENGTH(EMAIL) FROM EMPLOYEE;

-- EMPLOYEE 테이블의 이메일과 그 문자열의 길이를 반환\

 

LENGTHB(컬럼명 | 문자열 값) : 글자의 바이트 수를 리턴

SELECT LENGTHB('HELLO') FROM DUAL;

-- DUAL임시 테이블에서 HELLO 의 길이 값을 반환'5'

SELECT LENGTHB('원격 수업은...') FROM DUAL;

-- DUAL임시 테이블에서 '원격 수업은...'의 길이 값을 반환'9'

 

 

INSTR : 지정한 위치부터 지정한 숫자번째로 나타나는 문자의 시작 위치 반환

•      특정문자열에서 원하는 문자를 찾아서 위치를 반환
• 시작위치는 1부터 시작
• INSTR('문자열' | 컬럼명, '찾을 문자')
• 찾을 위치의 시작값: 양수 - 앞에서부터 검색, 음수 - 뒤에서부터 검색
• 빈도 (기본값 : 1) : 찾을 문자가 여러개인 경우 몇번째 문자를 찾을 것인지 지정

SELECT INSTR('ABCDEFGHI','C') FROM DUAL;

-- -> 'ABCDEFGHI' 문자열에서 'C'의 위치 찾기 : 3

 

SELECT INSTR('ABCDEFGHI','F',1) FROM DUAL;

-- -> 찾을 문자가 1개만 있을 때는 시작값에 상관없이 동일한 위치

SELECT INSTR('ABCDEFGHI','F',-1) FROM DUAL;

-- ->  찾을 문자가 1개만 있을 때는 시작값에 상관없이 동일한 위치

 

SELECT INSTR('ABFCDEFGHI','F',1) FROM DUAL;

-- -> 앞에서부터 찾을 때의 위치는 첫번째 F의 결과값 : 3

SELECT INSTR('ABFCDEFGHI','F',-1) FROM DUAL;

-- -> 뒤에서부터 찾을 때의 위치는 마지막 F의 결과값 : 7

 

SELECT INSTR('ABFCDEFGHI','F',1,1) FROM DUAL;

-- -> 앞에서부터 첫번째 위치한 'F'의 위치값 : 3

SELECT INSTR('ABFCDEFGHI','F',1,2) FROM DUAL;

-- -> 앞에서부터 두번째 위치한 'F'의 위치값 : 7

 

--EMAIL 에서 아이디의 길이 가져오기

SELECT EMAIL, INSTR(EMAIL, '@')-1 AS ID_LENGTH FROM EMPLOYEE;

 

LPAD, RPAD : 주어진 컬럼 문자열에 임의의 문자열을 덧붙여 반환 -- 특별 문자로 패딩이 들어감

 

LPAD('문자열'|  컬럼명, 반환할 문자열 길이, )

SELECT LPAD(EMAIL, 20, '#') FROM EMPLOYEE;

 

초이 길이는 20이 되고 모자란 빈 공간을 # 으로 채워서 반환

SELECT LPAD(EMAIL, 20) FROM EMPLOYEE;

 

SELECT RPAD(EMAIL, 20, '#') FROM EMPLOYEE;

 

SELECT RPAD(EMAIL, 20) FROM EMPLOYEE;

 

 

LTRIM/RTRIM : 주어진 컬럼이나 문자열 왼쪽/오른쪽에서 지정한 문자 혹은 문자열을 제거한 나머지를 반환

 

LTRIM/RTRIM('문자열' | 컬럼명 [, 제거할 문자])

SELECT LTRIM('        KH      ') FROM DUAL;

 

SELECT RTRIM('        KH      ') FROM DUAL;

 

SELECT LTRIM('KHKH1234KHKH', 'KH') FROM DUAL;

 

SELECT RTRIM('KHKH1234KHKH', 'KH') FROM DUAL;

 

SELECT TRIM('      KH      ') FROM DUAL;

 

SELECT RTRIM(LTRIM('      KH      ')) FROM DUAL;

 

TRIM

SELECT TRIM(LEADING FROM '    KH    ')FROM DUAL;

-- LTRIM 과 동일

 

SELECT TIRM(TRAILING FROM '      KH      ') FROM DUAL; -- RTRIM 과 동일

 

SELECT TRIM(BOTH FROM '    KH    ') FROM DUAL;

-- 양쪽 모두 제거

 

SELECT TRIM(BOTH 'KH' FROM 'KHKH1234KHKH') FROM DUAL;

-- 오류 : TRIM에서 지정 문자를 삭제할 때에는 문자'열'이 오면 안되고 한 개의 문자 만이 와야한다.

-- KH가 아닌 K만 왔을 때 가능 'trim set should have only one character'

 

SELECT TRIM(BOTH 'K' FROM 'KHKH1234KHKH') FROM DUAL; -- 한 글자만 와야함

 

SUBSTR : 컬럼이나 문자열에서 지정한 위치로부터 지정한 개수의 문자열을 잘라서 리턴

 

SUBSTR(문자열, 시작위치, 갯수)

SELECT SUBSTR('SHOWMETHEMONEY', 5,2) FROM DUAL;

--- ME

 

SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL;

-- THEMONEY : 7번째부터 끝까지

 

SELECT SUBSTR('SHOWMETHEMONEY', -8, 3)FROM DUAL;

-- THE : 끝에서 8번째 글자부터 3글자

 

주민번호에서 성별 조회

SELECT * FROM EMPLOYEE;

SELECT EMP_NAME, SUBSTR(EMP_NO, 8, 1) FROM EMPLOYEE;

 

 

LOWER/UPPER/INITCAP : 대소문자 변경

SELECT LOWER('Welcom To my World') FROM DUAL;

-- welcom to my world : 모두 소문자로 변환

 

SELECT UPPER('Welcom To my World') FROM DUAL;

--WELCOME TO MY WORLD : 모두 대문자 변환

 

SELECT INITCAP('welcome to my world') FROM DUAL;

--Welcome To My World : 스페이스를 기준으로 앞글자를 대문자로 변환

 

CONCAT : 문자열 혹은 컬럼을 입력받아 합친 후 리턴

SELECT CONCAT('가나다라', 'ABCD') FROM DUAL;

-- 가나다라ABCD

 

SELECT '가나다라' || 'ABCD' FROM DUAL;

-- 가나다라ABCD

 

REPLACE : 문자열을 변환해준다

SELECT REPLACE('WELCOME TO ORACLE', 'ORACLE', 'MYSQL')FROM DUAL;

 

--WELCOME TO ORACLE에서 ORACLE을 MYSQL 로 변경 -> WELCOME TO MYSQL

 

 

**연습 예제

직원 테이블에서 주민번호를 조회해서 생년, 생월, 생일을 각각 분리해서 조회

SELECT EMP_NAME 사원명,

    SUBSTR(EMP_NO, 1,2) 생년,

    SUBSTR(EMP_NO, 3,2) 생월,

    SUBSTR(EMP_NO, 5,2) 생일

    FROM EMPLOYEE;

   

여직원들의 모든 컬럼의 정보를 조회

SELECT * FROM EMPLOYEE;

 

-- 주민번호가 2로 시작하는 직원들을 조회 

SELECT * FROM EMPLOYEE

    WHERE SUBSTR(EMP_NO, 8,1) = 2;

 

-- 함수 중첩 사용 : 함수 안에서 또 다른 함수를 사용할 수 있음

-- 직원테이블에서 사원명, 주민벙호 조회

    -- 단, 주민번호는 생년월일만 보이게 하고, '-' 다음 값은 '*'로 변경

SELECT EMP_NAME 사원명,

    RPAD(SUBSTR(EMP_NO,1,INSTR(EMP_NO, '-')),LENGTH(EMP_NO), '*') 주민번호

    FROM EMPLOYEE;