Database SQL_Single-Row Function_문자함수

Step 2. Single-Row Function! 

- Sub : 문자함수

 

얼마만에 Step 2로 넘어온건지 모르겠네요..ㅎㅎ

이전에 Step 1인 Select는 모두 다 이해하고 스스로 예제도 더 만들어서 해보고 그러셨나요? 그래야 합니다. 안그러면 안늘어요!!!

이제부터는 조금 복잡 할 수도있는 함수부분을 들어갈겁니다. 먼저 Single-Row Function 이라고 했습니다.

단일 행 함수라고도 하죠 ㅎ 이건 행 하나하나하나에 대한 결과값을 나타내기 때문에 이렇게 부릅니다.

A처리하고 B처리하고 C처리하고 결과내는게 단일행 함수! 그럼 반대되는 개념은? Multiple-Row Function. 복수 행 함수라고하죠.

이건 그럼 어찌될까욤? 여러개의 행을 하나하나 하는 것이아니라 한꺼번에 처리를 해서 결과를 내는겁니다.

이후에 비교를 하면 확실히 느끼게 되실거에요 ㅎ 저도 한번에 이해는 못했으니까요..ㅎ 동병상련이죠 ㅎ

 

이제 단일행 함수를 본격적으로 들어가 볼께요 ㅎ

단일행 함수에는 여러가지 형태의 함수들이 존재합니다. 언제나 큰그림은 그리고 있어야해요. 어디에 뭐가 포함이 되는지에 대한.

안그러면 다 배우고 나서도 이게 어디에 속하지? 뭐에 속해서 이렇게 나오는거지? 라는 물음이 나오고 개념이 불확실 하다는 것이 나옵니다. 그렇기 때문에 전체적인 큰그림을 그리고 있다면 전혀 헷갈릴 것이 없겠죠 ㅎ 특히 Oracle은 더 그런거 같음..ㅎ

 

 

요로코롬 큰 그림을 그려야 합니다. 단일행 함수에는 문자, 숫자, 날짜, 변환, 일반이 있고 변환에는 묵시적 명시적이 있다는것!

그리고 이번 포스트에서는 문자 함수를 다룬다는 것을 알려드리고 시작하겠습니다.

왜 저렇게 나눴는지 궁금하시다구요? 예를 들어 화장실로 할께요..ㅎ 예전에도 화장실로 예를 든게 있었던거 같은데..ㅎ

화장실을 함수라고 할께요. 그리고 들어가는 것과 나오는것으로 하겠습니다. 사람이 화장실로 들어가면 나오는것은? 사람이죠

동물이 화장실로 들어갔다가 나오면? 동물이죠. 이것과 같이 문자가 들어갔다가 나오면 문자 함수죠. 문자가 들어갔다가 숫자로 나오는거?

변환함수에서는 그럴 수 있지만 일반적인 함수에서는 그럴수가 없습니다. 동작자체를 거부하니까요 ㅎㅎ 말이 또 길어지네요..

문자 함수 본격적으로 들어가 보도록 하겠습니다 ㅎ

 

문자 함수도 자세히 보면 두가지로 세분화를 시킬 수 있습니다.

하나는 Case-Manipulation : 대소문자 변환 함수이고 또다른 하나는 Character-Manipulation : 문자 변환 함수입니다.

여기에서 변환이라고해가지고 다른 숫자나 날짜로 바꾸는 것이 아닌 문자에서 문자로 바꾸는 것을 말합니다.

Case-Manipulation은 무엇이 있느냐 살펴보면 LOWER, UPPER, INITCAP이 있습니다.

Function	의미	사용 예
LOWER(column|expression)	입력 값을 전부 소문자로 변환	LOWER(ABC) ->abc
UPPER(column|expression)	입력 값을 전부 대분자로 변환	UPPER(abc) ->ABC
INITCAP(column|expression)	입력 값의 첫 글자만 대문자로 변환	INITCAP(abc)->Abc

 

잘 아시겠나요?ㅎ 여기서는 간단히 의미와 문법을 살펴보고 아래에서 자세히 예를 들고 실제로 해보면서 볼겁니다 ㅎ

Case-Manipulation을 했으니 Character-manipulation을 보겠습니다. 좀 많아요..ㅎ

Function	의미	사용 예
CONCAT(column1|expression1, column2|expression2)	두 문자열을 결합해서 출력	CONCAT('A','B') -> AB
SUBSTR(column|expression, m [, n])	주어진 문자에서 특정 문자만 추출	SUBSTR('ABC' , 1, 2) -> AB
SUBSTRB(column|expression, m [, n])	주어진 문자에서 특정 바이트만 추출	SUBSTRB('한글', 1 ,2) -> 한
LENGTH(column|expression)	입력된 문자열의 길이 값을 출력	LENGTH('한글') -> 2
LENGTHB(column|expression)	입력된 문자열의 바이트 값을 출력	LENGTH('한글') -> 4
INSTR(column|expression, 'string', [,m], [n])	주어진 문자에서 특정 문자의 위치 추출	INSTR('A#B','#') -> 2
INSTRB(column|expression, 'string', [,m], [n])	주어진 문자에서 특정 문자 위치 바이트 추출	INSTRB('A*B','*') -> 2
LPAD(column|expression, n, 'string')	주어진 문자열에서 왼쪽으로 특정문자 채움	LPAD('abcd', 6, '*') -> **abcd
RPAD(column|expression, n, 'string')	주어진 문자열에서 오른쪽에 특정문자 채움	RPAD('ABCD', 7, '#') -> ABCD###
LTRIM(leading|trailing|both, ltrim_character FROM ltrim_source)	주어진 문자열에서 왼쪽의 특정문자 삭제	LTRIM('*abc', '*') -> abc
RTRIM(leading|trailing|both, rtrim_character FROM rtrim_source)	주어진 문자열에서 오른쪽의 특정문자 삭제	RTRIMG('ABC*', '*') -> ABC
REPLACE(text, search_string, replacement_string)	주어진 문자열에서 A를 B로 치환함	REPLACE('AB', 'A', 'E') -> EB

 

이렇게 됩니다. 이후에 정규식을 이용하기 위한 함수도 있지만 우선 문자 함수에서는 여기까지만 하고 정규식은 마지막에 추가하거나 다음 포스트에서 하도록 하겠습니다.

 

이제 전체적인 것들을 살펴보았으니 자세히 Oracle을 키고 예들고 보면서 하겠습니다 ㅎ

Case-Manipulation Function은 어떤게 있다고했죵? LOWER, UPPER, INITCAP 이렇게 세개라고 했습니다. 한번 해보죠 ㅎ

 

 

 

한꺼번에 비교를 해봤습니다. 귀찮아서..는 아닙니다 ㅎㅎ 지금 emp 테이블의 ename을 가지고 세가지 함수로 비교해 봤는데요.

어떤가요?ㅎ 한눈에 들어오시나요? LOWER는 소문자로 UPPER는 대문자로 INITCAP은 맨앞글자만 대문자로! 신기방기하죠 ㅎ

중요한건 이걸 어디에 쓸 수 있을까 하는 것에 대한 의문이 남아야죠. 함수가 있으면 뭐합니까? 쓸데가 있어야죠 ㅎ

이런것들은 어디에 쓸수 있을까요? 회원가입 같은거? 소문자로 입력하라고 했는데 삐뚤어진 사람이 대문자로 쓸수도 있죠. 그런거를 소문자로 바꿔주는 센스같은!? 아니면 INITCAP 같은 경우에는 영문이를 쓸때 첫글자만 대문자로 바꾸는 경우가 많으니 그럴때도?ㅎ

함수를 쓸때에는 어디에 어떻게 쓸지도 생각하면 더욱더욱 좋겠죠 ㅎ

이것이 바로 SQL의 매력이랄까..ㅎ 대단한 매력인거 같아요 ㅋ.ㅋ 다음것도 한번 보죠!

 

한번에 Case-Manipulation Function을 끝냈으니 Character-Manipulation Function을 봐야겠죠!

그 첫번째는! CONCAT! 연결시켜주는 것이라고 했습니다.

CONCAT(column1|expression1, column2|expression2)

요것이 바로 문법 ㅎ 문법을 봤으니 어찌 쓰는지도 봐야겠죠? CONCAT은 이미 우리가 한번 써봤어요.

언제? 연산자 할때 || 요렇게 생긴게 있었죠? 연결연산자 합성연산자라고 부르는 이것!과 같은 역할이에요

 

 

이런 그림 어디서 봤다구요? 연산자에서 봤다구요 ㅎ 못보셨다구요? 제대로 안보신거에요  이런..ㅎ

돌아가서 한번 다시 보시고 오세요 ㅎ 대충대충넘어가면 실력이 절대절대절대 늘지 않을거에요.. 확실함다 ㅎㅎ

SELECT name||position FROM professor; 와 같은 결과물임을 다시 한번 알려드리면서~ㅎ 다음!

 

SUBSTR입니다. SUBSTR는 딱봐도 많이 쓰게 생긴 아이에요. 주어진 문자에서 특정 문자만 추출 하기때문에 용이하죠.

SUBSTR(column|expression, m [, n])

SUBSTRB(column|expression, m [, n])

문법은 요래요래 두개입니다. 하나는 문자를 추출 하는것, 또하나는 바이트를 추출하는것

한글의 경우에는 2바이트 영문자의 경우 1바이트로 치기 때문에 바이트 계산을 할 경우에는 무지하게 신경을 한번더!ㅎ

SUBSTR는 어디에서 많이 쓰일까요? 전 딱 주민등록번호를 생각했어요. 주민등록번호에서 우리는 생년월일, 성별을 기본적으로 알아 낼 수 있기 때문에 따로 입력하지 않아도 알 수 있는 그런 정보! 좋잖아요? 그런거 ㅎㅎ 나중에 조건부도 배울텐데 거기에서 쓰면 더 좋을 듯!

아는 것이 많으면 몸이 덜 고생하고 손이 덜고생하는 그러한 사태가 일어날 수 있습니다. 다들 겪어 보셨을 거라고 생각해요..ㅎ

SUBSTR를 예를 들어서 한번 해볼께요 ㅎ

 
이렇게 두가지를 예를들어서 한번 보겠습니다. SUBSTR에는 두가지 숫자가 들어갑니다. 처음 기준점을 잡는 숫자와 진행될 숫자.

처음 기준점 또한 마이너스로 적혀질 수 있습니다. 마이너스는 반대의 개념을 가지고 있으니까 뒤에서부터 본다고 보면 되겠죵ㅎ

한번 봅시당 ABCDE에서 기준점을 1로 잡았으니까 A가 되겠고 그것을 포함한 2니까 AB가 나오죠!

그럼 오른쪽은? -3이기 때문에 끝에서부터 하나~ 둘~ 셋~ 해서 C가 기준점이 되고 C를 포함해서 +2이기 때문에 CD가 나옵니다 ㅎ

어때요? 간단하죠?ㅎ 어렵지 않습니다 ㅎ 그럼 주민등록번호를 가지고 한번 해볼께요 ㅎ

 

학생의 정보가 들어있는 student 테이블에서 jumin 컬럼을 보면 주민등록번호가 나옵니다. 그중 생년월일을 출력하고싶다면??

위처럼 SUBSTR(jumin, 1, 6) 으로 잡아주면 생년월일이 나오게 되는거죠 ㅎ 물론 WHERE절에서도 SUBSTR를 쓸수 있습니다.

 

 

남자만 조회하고 싶은 경우에는 어떻게 하면 좋을까요? 생년월일 이후 주민등록번호의 7번째 자리는 성별을 나타내는 숫자죠 ㅎ

그렇기 때문에 이렇게 조건절에서 jumin의 7번째를 기준점으로 잡고 첫번째를 조회하는데 1인경우를 출력하시오 라고 하면 되겠죠!

이번에는 SUBSTRB까지 보도록 하겠습니다! 별로 다를 건없지만 이건 바이트로 계산 한다는 것!

 

 

 

처음 기준으로 잡는 숫자도 바이트로 계산하기 때문에 잘 해주어야 합니다. 지금은 첫번째를 기준으로 2개를 해서 위처럼 나오지만

예를들어 SUBSTRB(name, 2, 2) 라고 하면 아무런 것도 출력되지 않습니다. 한글이 2바이트인데 끝이 나는 3바이트부터 시작하지 않고 2바이트를 기준으로 잡으면 한글의 반이 어떻게 나오겠습니까? 그래서 아무런 값도 출력되지 않습니다. 그러므로 SUBSTRB(name, 3, 2) 라고 하면 정상적으로 두번째 글자가 출력이 되는 것을 볼 수 있습니다.

 

 

 

 

저도 직접해보고서 말하는거에요 ㅎ 헷갈릴땐 무조건 실습실습! 실습만이 살길입니다 ㅎㅎ 여러가지 방면으로 해보는게 좋아요 ㅎ

 

이제 중요한 SUBSTR를 넘어서서 LENGTH, LENGTHB를 설명하겠습니다. 이름에서부터 파바박 오죠?ㅎ 길이 ㅎㅎ

LENGTH(column|expression)

LENGTHB(column|expression)

이렇게 두가지 입니다. 간단하게 보고 넘어가죠 ㅎ

 

바이트 수 예로드는건 역시 한글이 최고죠 ㅎㅎ LENGTH를 봤을때는 글자가 세개니까 3! LENGTHB를 봤을때는 6바이트니까 6!

간단하게 길이보는것 넘어가고!ㅎ INSTR를 보겠습니다. SUBSTR와 비슷하기도하고 다르기도 한 INSTR!

INSTR(column|expression, 'string', [,m], [n])

INSTRB(column|expression, 'string', [,m], [n])

문법만 봐도 비슷비슷해 보입니다. 다만 INSTR가 더 많죠 ㅎ INSTR의 경우에는 추출하기 위함입니다.

추출? 무슨추출? 하실수도 있습니다. 위에 표에서 예를 들었듯이 '*'을 찾으라고 하면 별이 있는 위치를 말하는 등의 기능입니다.

문법을 한번 보시면 INSTR(컬럼|표현식, '문자열', 기준점, 몇번째인지) 라고 해석하시면됩니다.

예를들어 a*b*c*d라는 값을 가지는 한 문자열이 있다고 하면 찾고자하는 문자열을 '*'요렇게 넣으시고 기준점 1로 잡고 몇번째인지 자신이 찾고자하는 문자의 순서를 넣으면됩니다. b뒤에있는 *을 조회하고 싶다! 하면 INSTR('a*b*c*d', '*', 1, 2) 라고 하면 두번째 별을 잡습니다. 그냥 기준점 3으로 해서 b부터 시작하고 1로해도 무방~ 합니다 ㅎ 자신의 마음대로 짤 수 있는게 언어 아니겠습니까 ㅎ

한가지 SUBSTR와의 또다른 다른점은 기준점을 -(마이너스)로 잡았을 때입니다. SUBSTR의 경우 -로 잡으면 뒤에서부터 기준점을 잡고 다시 오른쪽으로 진행방향을 바꿨는데 INSTR는 그냥 -(마이너스) 방향으로 쭈우욱갑니다.

다시 위에 a*b*c*d로 예를 들자면 SUBSTR는 SUBSTR(a*b*c*d, -3, 2) 하면 c*이 나옵니다.

하지만 INSTR의 경우 INSTR(a*b*c*d, '*', -3, 1)을 하면 b뒤에있는 *의 위치인 4가 나옵니다.

이처럼 INSTR와 SUBSTR는 약간의 차이점이 있는거죠 ㅎ 또 실제로 한번 써보죠 ㅎ

 

 

이렇게 전화번호의 경우 서울은 02로 시작하기 때문에 다른 곳과 좀 다릅니다. 지역번호를 알기 위해서 이처럼 INSTR로 구분지어서 사용하시면 조금 더 편리 할 수 있습니다. INSTR의 설명이 좀 길었네요 ㅎ 다음은! PAD!

LPAD(column|expression, n, 'string')

RPAD(column|expression, n, 'string')

PAD 함수는 채우는 겁니다. 왼쪽으로 채울 것인지 오른쪽으로 채울것인지를 결정하는 것이죠 ㅎ 직접 보죵ㅎㅎ

 

 

요런그??ㅎ LPAD와 RPAD를 동시에 써봤습니다. PAD뒤에 숫자는 총 길이를 의미하는데요. 10에 딱 맞는 경우에는 뒤에 붙은 string을 추가시키지 않은 상태로 놔두고 10에 부족할 경우에만 string을 붙이는 것이 바로 PAD입니다.

표에서 PAD다음이 뭐죵? TRIM이죠?ㅎ TRIM은 추가시키는 것이 아니라 없애는 것!

LTRIM(leading|trailing|both, ltrim_character FROM ltrim_source)

RTRIM(leading|trailing|both, rtrim_character FROM rtrim_source)

추가시키는 것이 아닌 없애는 것이라 했죠?ㅎ 왼쪽을 없애느냐 오른쪽을 없애느냐의 차이!ㅎ

 

이번에도 한눈에 볼 수 있게끔!!ㅎ LTRIM RTRIM 둘다 썼습니다. LTRIM은 왼쪽에 있는 정이라는 글씨를 빼서 정교수 -> 교수

RTRIM은 오른쪽에 강사를 빼서 전임강사 -> 전임 이라고 했습니다. 조금 이상하더라도 예를 든거니까요..ㅎ 이것도 어디에 쓸지 생각생각

 

표에서의 마지막! 뭐였죠?ㅎ REPLACE! 치환함수였죠 ㅎ 치환이란 원래 있던 것을 다른 것으로 바꿔서 나타내는것이죠.

REPLACE(text, search_string, replacement_string)

문법도 간단하네요 ㅎ 치환할 것만 딱딱 해주면 되니까요 ㅎㅎ

 

 

이번 REPLACE는 간단하게 교수는 건방져보이니까 교수님이라고 할께요 ㅎ 어감이 훨씬 좋네요 ㅎ 보기도 좋고 하핳ㅎㅎ

물론 이렇게 REPLACE를 한다고 원본데이터가 치환이 되는 것이 아니라 보이는 것만 치환이 되는 겁니다!ㅎ 아시겠죵?ㅎ

이렇게 표에 있는 모든 문자함수를 다뤄 봤어요.. 다음에는 아마 이번에서 다루지 않은 정규식과 숫자, 날짜 함수가 되지 않을까~ㅎ

우선 문자함수는 이렇게 끗! 뿅!ㅎ 좀있다가 아마 Admin이 올라갈거 같네요 ㅎ Admin은 올리는게 좀 걸려서.. 이해하는 것도 그렇고ㅜ

좀 있다 뵙죵 ㅎ