[오라클 SQL] 문자 함수

문자 함수 (Character Funtions)

• 연산 대상이 문자.
• 입력 인수가 VARCHAR2 인 경우 반환값도 VARCHAR2.

 

LOWER(char)	모든 영문자를 소문자로 변환하여 반환
UPPER(char)	모든 영문자를 대문자로 변환하여 반환
INITCAP(char)	첫 글자는 대문자, 다른 모든 글자는 소문자로 변환하여 반환
CHR(n [USING NCHAR_CS])	DB 문자 세트 또는 USING NCHAR_CS로 지정한 문자 세트에서 n의 ASCII 코드값을 갖는 문자를 VARCHAR2로 반환
ASCII(char)	char의 첫 번째 문자의 10진수 코드값 반환
ASCIISTR(char)	DB 문자 집합에서 문자열의 ASCII 버전을 반환. 한글 등의 non-ASCII 문자는 UTF-16 code로 반환
SUBSTR(char, position [, substring_length])	char문자열에서 position부터 시작하여 substring_length길이의 문자열 반환.
LENGTH(char)	char의 길이 반환. LENGTHB()는 문자 대신 바이트 사용.
INSTR(string, substring [, position, occurrence])	string에서 substring 검색하여 문자열의 위치 반환. 문자열이 없으면 0 반환. 첫번째 문자열 위치 인덱스는 1. position:검색할 시작 위치, occurrence:검색 문자열 발생 횟수. 기본값은 둘 다 1.

 

REPLACE(char, search_string [, position replacement_string]])	char에서 모든 search_string을 replacement_string로 비한된 char 반환. replacement_string 생략하거나 null이면, search_string 제거.
CONCAT(char1, char2)	char1과 char2 결합하여 반환
LPAD(exp1, n, [, expr2])	남은 왼쪽 여백을 expr2로 채우고 길이가 n인 expr1 반환. expr2 지정하지 않으면 기본값은 단일 공백
RPAD(exp1, n, [, expr2])	남은 오른쪽 여백을 expr2로 채우고 길이가 n인 expr1 반환. expr2 지정하지 않으면 기본값은 단일 공백
LTRIM(char, [,set])	char 왼쪽 끝에서 set에 포함된 모든 문자 제거. set 지정 안하면 기본값은 단일 공백
RTRIM(char, [,set])	char 오른쪽 끝에서 set에 포함된 모든 문자 제거. set 지정 안하면 기본값은 단일 공백
TRIM([ [LEADING|TRAILING|BOTH] trim_character FROM] trim_score)	문자열에서 선행(leading)이나 후행(trailing) 또는 선행과후행(both)문자 제거. both 지정하거나 생략하면 both.
TRANSLATE(expr, from_string, to_string)	from_string의 각 문자가 to_string의 해당 문자로 교체된 expr 반환. from_string에 없는 expr의 문자는 대체되지 않음

 

 

• LOWER(char): 소문자
  • SELECT LOWER('Seoul'), LOWER('KOREA') FROM dual;
  • SELECT * FROM col WHERE tname = 'emp'; -- 출력되는 행 없음. 테이블명은 전부 대문자
  • SELECT * FROM col WHERE tname = 'EMP';

 

• UPPER(char): 대문자
  • SELECT UPPER('Seoul'), UPPER('KOREA') FROM dual;
  • SELECT * FROM col WHERE tname = UPPER('emp');

 

• INITCAP(char): 첫글자만 대문자로, 나머지는 소문자 (단어별 첫글자 대문자)
  • SELECT INITCAP('KOREA seoul') FROM dual; -- Korea Seoul

 

• CHR(n [ USING NCHAR_CS ]): ascii 코드에 해당하는 문자
  • SELECT CHR(75) || CHR(79) FROM dual;

 

• ASCII(char): 첫문자의 데이터베이스 문자 세트에서 10진수 코드값
  • SELECT ASCII('ABC') FROM dual;  -- 65

 

• ASCIISTR(char): 영문자와숫자는 그대로 출력되고 나머지 non-asc 문자는 유니코드 출력
  • SELECT ASCIISTR('korea 123'), ASCIISTR('대한') FROM dual;

 

• SUBSTR(char, position [, substring_length ]): 특정 위치의 문자열 반환
  • 인덱스는 1부터 시작. JAVA와 혼동 주의
  • SELECT SUBSTR('Seoul Korea, 7, 3) FROM dual; -- Kor 7번째부터 3개 
  • SELECT SUBSTR('Seoul Korea, -5, 3) FROM dual; -- Kor -5:뒤에서부터 5번째
  • SELECT SUBSTR('Seoul Korea, 7) FROM dual; -- Korea 7번째부터 끝까지

-- yymmdd-s s가 1,2,5,6이 90년대 서울사람 (rrn:101010-2xxxxxx)
-- 1900s년대 서울사람. name, rrn, city, sal	
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(1,2,5,6) AND city='서울';
		
-- 2000년대 사람. name, rrn, city, sal	
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(3,4,7,8) AND city='서울';
		  
-- 78~82년생. 
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn, 1,2)>='78' AND SUBSTR(rrn, 1,2)<='82';
		  
-- 서울사람 이면서 김씨
SELECT name, rrn, city, sal
FROM emp
WHERE city='서울' AND SUBSTR(name,1,1)='김';
		  
-- 80~89년생
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn, 1,1)=8;
		  
-- 김씨, 이씨, 최씨
SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(name,1,1) IN ('김','이','최');
			
-- 홀수달에 태어한 사람 중 sal 내림차순
SELECT name, rrn, city, sal
FROM emp
WHERE MOD(SUBSTR(rrn,4,1),2)=1
ORDER BY sal DESC;

 

• INSTR(string , substring [, position [, occurrence ] ]): 문자열 검색하여 위치를 반환. 없으면 0
  • SELECT INSTR('korea seoul', 'e') FROM dual; -- 4
    • -- 처음부터 검색하여 가장 처음 만나는 위치 
  • SELECT INSTR('korea seoul', 'abc') FROM dual; -- 0. 없으면 0
  • SELECT INSTR('korea seoul', 'e', 7) FROM dual; -- 8
    • -- 7번째 위치(seoul)부터 검색
  • SELECT INSTR('korea seoul', 'e', 1, 2) FROM dual; -- 8
    • -- 처음부터 검색하여 두번째 나오는 위치

-- 이름에 '이' 라는 단어가 포함된 사람. name, tel, sal
SELECT name, tel, sal
FROM emp
WHERE INSTR(name, '이')>0;
			
-- name, tel, tel 서비스번호, tel 국번, tel 번호
-- 이런 문제는 자바에서 분리하는 것이 효율적.
SELECT name, tel, 
	SUBSTR(tel, 1, INSTR(tel,'-')-1) 서비스,
	SUBSTR(tel, INSTR(tel,'-')+1,INSTR(tel,'-',1,2)-INSTR(tel,'-')-1) 국번,
	SUBSTR(tel, INSTR(tel,'-',1,2)+1) 번호
FROM emp;

 

 

• LENGTH(char): 문자열의 길이. 한글도 1자씩 출력
  • SELECT LENGTH('대한민국' ) FROM dual; -- 4
  • SELECT LENGTHB('대한민국' ) FROM dual; -- 12. byte 수 출력. 한글은 UTF-8로 저장됨

 

• REPLACE(char, search_string [, replacement_string]): 치환
  • SELECT REPLACE('seoul korea', 'seoul', 'busan') FROM dual;
    • -- busan korea
  •  SELECT REPLACE('123455245853', 5) FROM dual; -- 모든 5 제거
• CONCAT(char1, char2): 문자열 결합 (||와 동일)
  • SELECT '서울'||'부산' FROM dual;
  • SELECT CONCAT('서울','부산') FROM dual;

 

• LPAD(expr1, n [, expr2]): 남는 왼쪽 공간에 expr2로 채움
• RPAD(expr1, n [, expr2]): 남는 오른쪽 공간에 expr2로 채움
  • SELECT LPAD('korea',12,'*') FROM dual; -- *******korea
  • SELECT RPAD('korea',12,'*') FROM dual; -- korea*******
  • SELECT LPAD('korea',3,'*') FROM dual; -- kor
  • SELECT LPAD('korea',0,'*') FROM dual; -- null
  • SELECT LPAD('대한', 6, '*') FROM dual; -- **대한 LPAD는 한글은 2칸으로 처리

-- name, rrn, rrn(성별 다음부터는 * 로)
SELECT name, rrn, RPAD(SUBSTR(rnn,1,8),14,'*') 
FROM emp;
		-- 성별 다음인 '-1or-2'의 위치를, 찾고 총 길이까지 오른쪽에서부터 * 채우기
		
-- name, tel. 단 전화번호는 뒤 3자리는 *출력
SELECT name, RPAD(SUBSTR(tel,1,LENGTH(tel)-3), LENGTH(tel),'*')
FROM emp;
			
-- last_name을 9자리로 출력하고 남는 자리는 해당 자릿수에 해당하는 숫자 출력
WITH tb AS (
	SELECT 'seoul' last_name FROM dual
		UNION ALL
	SELECT 'haha' last_name FROM dual
		UNION 
	SELECT 'no' last_name FROM dual    
) 
SELECT name, tel
FROM tb;

SELECT name, sal, LPAD('*', TRUNC(sal/10000), '*') 그래프
FROM emp;
			
-- 이름 마킹 
	ex) 김호->김*호, 나가다->나*다, 호가나다-> 호**다
	두글자 이름 유의. CASE문으로 2글자일때와 아닐때 구분
	-- 이름 길이의 처음하고 끝 어디에 저장 SUBSTR('123456789', LENGTH(last_name)+1)
				
	WITH tb AS (
		SELECT '김호' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '너자바' name, '010-1234-4568' tel FROM dual
			UNION ALL
		SELECT '우리나라' name, '032-888-7777' tel FROM dual
			UNION ALL
		SELECT '이대한' name, '010-1234-567.' tel FROM dual
			UNION ALL
		SELECT '호하하' name, '010-4588-4455' tel FROM dual
	)
	SELECT name, tel
	FROM tb;
	 -- 동일
	WITH tb AS (
		SELECT '김호' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '너자바' name, '010-1234-4568' tel FROM dual
			UNION ALL
		SELECT '우리나라' name, '032-888-7777' tel FROM dual
			UNION ALL
		SELECT '이대한' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '호히히' name, '010-4588-4455' tel FROM dual
	) 
	SELECT name, tel,
	CASE WHEN LENGTH(name)='2' THEN '*' ELSE LPAD('*',LENGTH(name)-2,'*')END
		||SUBSTR(name,-1,1) 
	END 이름
	FROM tb;
	 -- 동일
	WITH tb AS (
		SELECT '김호' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '너자바' name, '010-1234-4568' tel FROM dual
			UNION ALL
		SELECT '우리나라' name, '032-888-7777' tel FROM dual
			UNION ALL
		SELECT '이대한' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '호히히' name, '010-4588-4455' tel FROM dual
	) 
	SELECT name, tel,
		SUBSTR(name,1,1)||NVL(LPAD('*', LENGTH(name)-2,'*'),'*')||SUBSTR(name,-1,1)
	END 이름
	FROM tb;
				
	WITH tb AS (
		SELECT '김호' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '너자바' name, '010-1234-4568' tel FROM dual
			UNION ALL
		SELECT '우리나라' name, '032-888-7777' tel FROM dual
			UNION ALL
		SELECT '이대한' name, '010-1234-567' tel FROM dual
			UNION ALL
		SELECT '호히히' name, '010-4588-4455' tel FROM dual
	) 
	SELECT name, tel,
	CASE WHEN LENGTH(name)='2' 
			THEN SUBSTR(name,1,1)||'*'||SUBSTR(name,-1,1) 
		WHEN LENGTH(name)>'2' 
			THEN SUBSTR(name,1,1)||LPAD('*',LENGTH(name)-2,'*')||SUBSTR(name,-1,1)
	END 이름
	FROM tb;

 

• LTRIM(char [,set])
• RTRIM(char [,set])
• TRIM([[LEADING | TRAILING | BOTH] trim_character FROM] trim_score)
  • SELECT ':' || LTRIM('   우리  나라   ')||':'FROM dual; -- 왼쪽 공백 없애기
  • SELECT ':' || RTRIM('   우리  나라   ')||':'FROM dual; -- 오른쪽 공백 없애기
  • SELECT ':' || TRIM('   우리  나라   ')||':'FROM dual; -- 양쪽 공백 없애기
  • SELECT ':' || TRIM('   우리  나라   ', ' ','')||':'FROM dual; -- 모든 공백 없애기

-- '부'를 '팀'으로 바꾸기
	-- SELECT name, REPLACE(dept, '부', '팀') dept FROM emp;
	-- 모든 '부'를 '팀'으로 변경. 부서 이름에 부가 들어가는 경우 안됨
SELECT name, RTRIM(dept,'부') -- 오른쪽 '부' 제거
SELECT name, RTRIM(dept,'부') || '팀' dept FROM emp;
		 
SELECT LTRIM('AABBBCDCD', 'BA') FROM dual; -- CDCD
	-- B나 A 중 앞부분에 한 문자라도 있으면 제거
SELECT LTRIM('ABBBCADCD', 'BA') FROM dual; -- CADCD
		
SELECT TRIM('A' FROM 'AABBACCAA') FROM dual; -- BBACC
	-- 양쪽 A 제거. 가운데 껴있는 A는 제거 못함.
			
SELECT TRIM(LEADING 'A' FROM 'AABBACCA') FROM dual; -- BBACCA

 

• TRANSLATE(expr, from_string, to_string): REPLACE와 유사
  • SELECT TRANSLATE('abcabccc', 'c', 'n') FROM dual; -- abnabnnn
  • SELECT TRANSLATE('abcabccc', 'abc', 'ab') FROM dual; -- abab
  • SELECT REPLACE('abcabccc', 'c') FROM dual; -- abab
    •  -- abc 중에서 c만 없애기
  • SELECT TRANSLATE('2AB23C', '0123456789ABCDEFGHIJKLMNOPQRVYWXZ','999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') FROM dual;   -- 9XX99X
    • -- 숫자는 9, 영문자는 X로 바꾸기
  • SELECT TRANSLATE('2AB23C', '0123456789ABCDEFGHIJKLMNOPQRVYWXZ','0123456789') FROM dual; 
    • -- 223