[MySQL] 문자열 관련 함수 [SELECT]

❓SQL 함수란?

• 저장되어 있는 데이터를 집계하거나 조회, 저장, 수정하는 과정에서 값을 가공하기위하여 제공되는 모듈화 된 기능
• 각 DBMS에 따라 차이를 보이지만, 기본적으로 많이 사용되는 함수들은 공통으로 포함하고 있다.

📁 함수의 사용 방법

• 데이터 조회 시 👉 조회하고자 하는 컬럼의 값을 함수로 가공하거나 검색조건의 값을 지정할 때 사용한다.

SELECT 함수이름(컬럼)
FROM 테이블명
[WHERE 함수가 적용된 검색조건]

☕ JAVA에서 구현?

• SELECT절은 전부 JAVA로 넘어간다.
• 데이터의 필드를 POJO클래스로 정의
• 필드의 이름(컬럼명)이 멤버변수가 된다.
• 테이블 이름이 클래스가 된다.
• 행 1줄이 POJO클래스의 객체 1개가 된다.
• 행이 여러개 👉 List<POJO>

☕ JAVA로 데이터 불러오기

• 예를 들어 이름 3글자가 있을때, 김** 으로 출력해야한다고 가정하면.

  1. 원본 데이터를 그대로 가져와서 반복문 돌면서 문자열처리를 하는 방법
  2. DB에서 조회할때 별표가 쳐진 상태로 가공해서 SELECT를 해오는 방법

  👉 2번이 훨씬 편할 것이다 !

📁 문자열 관련 함수

📚 문자열 관련 함수의 종류

• 아래의 표에서 값은 컬럼 이름으로도 지정이 가능하다.

함수 이름	설명
left(값, 길이)	주어진 값을 길이의 글자 수 만큼 왼쪽에서 잘라낸다.
right(값, 길이)	주어진 값을 길이의 글자 수 만큼 오른쪽에서 잘라낸다.
substring(값, 시작위치, 길이)	주어진 값을 시작위치부터 길이만큼 잘라낸다. 만약 길이가 주어지지 않은 경우 시작위치부터 끝까지 잘라낸다.
replace(값, A, B)	주어진 값에서 A를 찾아 B로 바꾼다
concat(값1, 값2, …, 값n)	주어진 값들을 하나의 문자열로 연결한다.
trim(값)	주어진 값의 앞뒤 공백을 제거한다.
ltrim(값)	주어진 값의 왼쪽 공백을 제거한다.
rtrim(값)	주어진 값의 오른쪽 공백을 제거한다.
md5(값)	주어진 값을 암호화 한다.
char_length(값)	주어진 값의 글자수를 리턴한다.
instr(값, 찾을 내용)	주어진 값에서 찾을 내용이 시작되는 위치를 리턴한다. 찾지 못할 경우 0을 리턴한다.
upper(값)	주어진 값을 대문자로 변경한다
lower(값)	주어진 값을 소문자로 변경한다.

예제 1 : LEFT

• 학생 테이블에서 학생의 이름과 성(이름의 첫 글자)을 조회하시오.

SELECT name, left(name, 1) FROM student;
+--------+---------------+
| name   | left(name, 1) |
+--------+---------------+
| 전인하 | 전            |
| 박미경 | 박            |
| 김영균 | 김            |
| 지은경 | 지            |
| 임유진 | 임            |
| 서재진 | 서            |
| 이광훈 | 이            |
| 류민정 | 류            |
| 김진영 | 김            |
| 오유석 | 오            |
| 하나리 | 하            |
| 윤진욱 | 윤            |
| 이동훈 | 이            |
| 박동진 | 박            |
| 김진경 | 김            |
| 조명훈 | 조            |
+--------+---------------+
16 rows in set (0.00 sec)

• left(값,길이) : 주어진 값을 길이의 글자 수 만큼 왼쪽에서 잘라낸다.

☕ JAVA

substring(startIndex, endIndex) : startIndex(포함)부터 endIndex(불포함)까지의 문자열을 리턴(잘라내기)

예제 2 : RIGHT

• 학생 테이블에서 학생의 이름과 이름의 마지막 글자를 조회하시오.

SELECT name, right(name, 1) FROM student;
+--------+----------------+
| name   | right(name, 1) |
+--------+----------------+
| 전인하 | 하             |
| 박미경 | 경             |
| 김영균 | 균             |
| 지은경 | 경             |
| 임유진 | 진             |
| 서재진 | 진             |
| 이광훈 | 훈             |
| 류민정 | 정             |
| 김진영 | 영             |
| 오유석 | 석             |
| 하나리 | 리             |
| 윤진욱 | 욱             |
| 이동훈 | 훈             |
| 박동진 | 진             |
| 김진경 | 경             |
| 조명훈 | 훈             |
+--------+----------------+
16 rows in set (0.00 sec)

☕ JAVA

문자열이 s일때
p=s.length(); 👉 문자열의 길이
s.substring(p-1);

• 마지막글자부터 1글자 끝까지

예제 3 : SUBSTRING

• 학생 테이블에서 이름과 이름의 두 번째 글자를 조회하시오.

SELECT name, substring(name, 2, 1) FROM student;
+--------+-----------------------+
| name   | substring(name, 2, 1) |
+--------+-----------------------+
| 전인하 | 인                    |
| 박미경 | 미                    |
| 김영균 | 영                    |
| 지은경 | 은                    |
| 임유진 | 유                    |
| 서재진 | 재                    |
| 이광훈 | 광                    |
| 류민정 | 민                    |
| 김진영 | 진                    |
| 오유석 | 유                    |
| 하나리 | 나                    |
| 윤진욱 | 진                    |
| 이동훈 | 동                    |
| 박동진 | 동                    |
| 김진경 | 진                    |
| 조명훈 | 명                    |
+--------+-----------------------+

• substring(값, 시작위치, 길이)
• 만약 길이가 주어지지 않은 경우 시작위치부터 끝까지 잘라낸다.

☕ JAVA

SQL과 JAVA의 다른점
1. 시작위치가 SQL은 1부터 시작. JAVA는 0부터 시작.
2. JAVA는 (1,2) : 파라미터 두개가 모두 문자열의 인덱스 번호. SQL은 두번째 파라미터가 글자수.

예제 4 : REPLACE

• 학생 이름과 이름에서 ‘이’를 ‘lee’로 변경한 값을 출력하시오.

SELECT name, REPLACE(name, '이', 'lee') FROM student;
+--------+----------------------------+
| name   | replace(name, '이', 'lee') |
+--------+----------------------------+
| 전인하 | 전인하                     |
| 박미경 | 박미경                     |
| 김영균 | 김영균                     |
| 지은경 | 지은경                     |
| 임유진 | 임유진                     |
| 서재진 | 서재진                     |
| 이광훈 | lee광훈                    |
| 류민정 | 류민정                     |
| 김진영 | 김진영                     |
| 오유석 | 오유석                     |
| 하나리 | 하나리                     |
| 윤진욱 | 윤진욱                     |
| 이동훈 | lee동훈                    |
| 박동진 | 박동진                     |
| 김진경 | 김진경                     |
| 조명훈 | 조명훈                     |
+--------+----------------------------+
16 rows in set (0.00 sec)

☕ JAVA

JAVA 👉 replace
JS 👉 replaceAll

각각의 언어에서 대응되는 부분 비교해보기!

예제 5 : CONCAT

• SQL은 문자열+문자열일때 문자열 연결을 안함 👉 해결 : CONCAT

• CONCAT(컬럼1, 컬럼2) : 컬럼1+컬럼2 문자열 연결.

• 파라미터의 갯수 제한이 없다. (컬럼1, 컬럼2,..,컬럼n)

• 학생 이름과 학년을 하나의 문장으로 합쳐 출력하시오.

SELECT CONCAT(name, grade) FROM student;
+---------------------+
| CONCAT(name, grade) |
+---------------------+
| 전인하4             |
| 박미경1             |
| 김영균3             |
| 지은경2             |
| 임유진2             |
| 서재진1             |
| 이광훈4             |
| 류민정2             |
| 김진영2             |
| 오유석4             |
| 하나리1             |
| 윤진욱3             |
| 이동훈1             |
| 박동진1             |
| 김진경2             |
| 조명훈1             |
+---------------------+
16 rows in set (0.00 sec)

• name필드와 grade필드를 문자열 연결

참고

• Oracle은 파라미터 무조건 두개만 가능
• A,B,C 연결 👉
  MySQL : CONCAT(A,B,C)
Oracle : CONCAT(CONCAT(A,B) ,C) 또는 A||B||C (문자열 연결 연산자)

예제 6 : CONCAT

• 학생의 이름과 학년을 ‘전인하 4학년’의 형식으로 출력하시오.

SELECT CONCAT(name, ' ', grade, '학년') FROM student;
+----------------------------------+
| CONCAT(name, ' ', grade, '학년') |
+----------------------------------+
| 전인하 4학년                     |
| 박미경 1학년                     |
| 김영균 3학년                     |
| 지은경 2학년                     |
| 임유진 2학년                     |
| 서재진 1학년                     |
| 이광훈 4학년                     |
| 류민정 2학년                     |
| 김진영 2학년                     |
| 오유석 4학년                     |
| 하나리 1학년                     |
| 윤진욱 3학년                     |
| 이동훈 1학년                     |
| 박동진 1학년                     |
| 김진경 2학년                     |
| 조명훈 1학년                     |
+----------------------------------+
16 rows in set (0.00 sec)

• name 뒤에 공백으로 연결 + grade 뒤에 학년 👉 문자열 연결

예제 7 : TRIM

• 학생의 이름에서 앞뒤 공백을 제거한 값을 출력하시오.

SELECT trim(name) FROM student;
+------------+
| trim(name) |
+------------+
| 전인하     |
| 박미경     |
| 김영균     |
| 지은경     |
| 임유진     |
| 서재진     |
| 이광훈     |
| 류민정     |
| 김진영     |
| 오유석     |
| 하나리     |
| 윤진욱     |
| 이동훈     |
| 박동진     |
| 김진경     |
| 조명훈     |
+------------+
16 rows in set (0.00 sec)

• 앞 뒤 공백 제거

☕ JAVA

JAVA에서도 trim()함수를 쓴다.

예제 8 : LTRIM

• 학생 이름에서 왼쪽 공백을 제거한 값을 출력하시오.

SELECT LTRIM(name) FROM student;
+-------------+
| LTRIM(name) |
+-------------+
| 전인하      |
| 박미경      |
| 김영균      |
| 지은경      |
| 임유진      |
| 서재진      |
| 이광훈      |
| 류민정      |
| 김진영      |
| 오유석      |
| 하나리      |
| 윤진욱      |
| 이동훈      |
| 박동진      |
| 김진경      |
| 조명훈      |
+-------------+
16 rows in set (0.00 sec)

• 왼쪽 공백 제거

예제 9 : RTRIM

• 학생의 이름에서 오른쪽 공백을 제거한 값을 출력하시오.

SELECT RTRIM(name) FROM student;
+-------------+
| RTRIM(name) |
+-------------+
| 전인하      |
| 박미경      |
| 김영균      |
| 지은경      |
| 임유진      |
| 서재진      |
| 이광훈      |
| 류민정      |
| 김진영      |
| 오유석      |
| 하나리      |
| 윤진욱      |
| 이동훈      |
| 박동진      |
| 김진경      |
| 조명훈      |
+-------------+
16 rows in set (0.00 sec)

• 오른쪽 공백 제거

예제 10 : md5

• 학생의 이름을 암호화 한 결과를 출력하시오.

SELECT md5(name) FROM student;
+----------------------------------+
| md5(name)                        |
+----------------------------------+
| 502a9ea0d0b99944ff2e01e1eb509f3f |
| 167c1446b6031e7e9ea19b2e61071718 |
| a965195d8ff7f2578ded7023158551d6 |
| fa4cf978744d94bb0f671a072c2164f9 |
| e3e7062eec14e85b3c599f509de7ede7 |
| 718752ea6162894807162c0fcad90279 |
| 37d2073e95928ba8bb6ceec546e3feff |
| 0ec9c231ae3c73a9d45cffdb81094c79 |
| c444ea4d425b22e4398b226be9d6b185 |
| aa4a066ee8459d526b5fb608998e412d |
| 9a9ddb33cc169386c0dc17af7afd0127 |
| 2607dca37796e50ee192cf075f41fe02 |
| da78898e4f770ca67155f0f0c569b471 |
| 9a3ec7fa19a04a6dc3036509b1779a21 |
| ee84f899be798f93181c6db5916c6fbd |
| 82b343678cdec365475d3083c1eee774 |
+----------------------------------+
16 rows in set (0.00 sec)

• name필드에 있는 값을 암호화해서 출력
• 함수들은 원본을 가공해서 바꾸는 것이지 원본이 바뀌는 것은 아님.
• MySQL 5.X이하 버전에서는 PASSWORD()함수를 사용한다.

참고

• 암호화가 되면 복호화를 지원하지 않음
• 원본값이 무엇인지 알수없음
• 조회에서는 사용하지 않고, 저장이나 수정할때 사용한다.
• 사용자가 입력한 값을 md5로 가공해서 저장한다 👉 암호화해서 저장하는 것이다.

민감한 개인정보는 암호화해서 저장해야함.

비밀번호 찾기가 아니라 비밀번호 재발급을 하는 이유

• 암호화되어있기 때문에 원본값을 찾을 수 없기때문이다. (복호화 지원안하기 때문)

예제 11 : CHAR_LENGTH

• 학생 이름의 글자수를 조회하시오.

SELECT CHAR_LENGTH(name) FROM student;
+-------------------+
| CHAR_LENGTH(name) |
+-------------------+
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
|                 3 |
+-------------------+
16 rows in set (0.01 sec)

☕ JAVA

• length()

예제 12 : INSTR

• 학생 이름에서 ‘이’라는 글자가 나타나는 위치와 이름을 조회하시오.

SELECT INSTR(name, '이'), name FROM student;
+-------------------+--------+
| INSTR(name, '이') | name   |
+-------------------+--------+
|                 0 | 전인하 |
|                 0 | 박미경 |
|                 0 | 김영균 |
|                 0 | 지은경 |
|                 0 | 임유진 |
|                 0 | 서재진 |
|                 1 | 이광훈 |
|                 0 | 류민정 |
|                 0 | 김진영 |
|                 0 | 오유석 |
|                 0 | 하나리 |
|                 0 | 윤진욱 |
|                 1 | 이동훈 |
|                 0 | 박동진 |
|                 0 | 김진경 |
|                 0 | 조명훈 |
+-------------------+--------+
16 rows in set (0.00 sec)

• SQL은 인덱스 번호를 1부터 시작
• '이'라는 문자열이 시작되는 위치가 1부터다.
• 없으면 0

☕ JAVA

• indexOf : 문자열 찾지 못하면 -1 리턴

예제 13 : UPPER

• 학생의 아이디를 대문자로 변경하여 조회하시오.

SELECT UPPER(userid) FROM student;
+---------------+
| UPPER(userid) |
+---------------+
| JUN123        |
| ANSEL414      |
| MANDU         |
| GOMO00        |
| YOUJIN12      |
| SEOLLY        |
| HURIKY        |
| CLEANSKY      |
| SIMPLY        |
| YOUSUK        |
| HANAL         |
| SAMBA7        |
| DALS          |
| PING2         |
| LOVELY        |
| RADER214      |
+---------------+
16 rows in set (0.00 sec)

☕ JAVA

• toUpperCase()

예제 14 : LOWER

• 학생의 아이디를 소문자로 변경하여 조회하시오.

SELECT LOWER(userid) FROM student;
+---------------+
| LOWER(userid) |
+---------------+
| jun123        |
| ansel414      |
| mandu         |
| gomo00        |
| youjin12      |
| seolly        |
| huriky        |
| cleansky      |
| simply        |
| yousuk        |
| hanal         |
| samba7        |
| dals          |
| ping2         |
| lovely        |
| rader214      |
+---------------+
16 rows in set (0.00 sec)

☕ JAVA

• toLowerCase()

💻 연습문제

student table

SELECT name, idnum FROM student;
+--------+---------------+
| name   | idnum         |
+--------+---------------+
| 전인하 | 7907021369824 |
| 박미경 | 8405162123648 |
| 김영균 | 8103211063421 |
| 지은경 | 8004122298371 |
| 임유진 | 8301212196482 |
| 서재진 | 8511291186273 |
| 이광훈 | 8109131276431 |
| 류민정 | 8108192157498 |
| 김진영 | 8206062186327 |
| 오유석 | 7709121128379 |
| 하나리 | 8501092378641 |
| 윤진욱 | 7904021358671 |
| 이동훈 | 8312101128467 |
| 박동진 | 8511241639826 |
| 김진경 | 8302282169387 |
| 조명훈 | 8412141254963 |
+--------+---------------+
16 rows in set (0.00 sec)

연습문제 1-1

• 학생의 이름과 가운데 글자를 *로 변경한 이름을 조회하시오
• 단 이름은 3글자로만 구성된다고 가정

SELECT REPLACE (LEFT(name,3), (SUBSTRING(name,2,1)), '*') `학생 이름` FROM student;
+-----------+
| 학생 이름 |
+-----------+
| 전*하     |
| 박*경     |
| 김*균     |
| 지*경     |
| 임*진     |
| 서*진     |
| 이*훈     |
| 류*정     |
| 김*영     |
| 오*석     |
| 하*리     |
| 윤*욱     |
| 이*훈     |
| 박*진     |
| 김*경     |
| 조*훈     |
+-----------+
16 rows in set (0.00 sec)

연습문제 1-2

• 학생의 이름과 주민번호를 조회하시오
• 단 주민번호의 뒤 자리 7자리는 '*'로 표시하시오

SELECT name, REPLACE (idnum, (SUBSTRING(idnum,7)), '*') `주민번호` FROM student;
+--------+----------+
| name   | 주민번호 |
+--------+----------+
| 전인하 | 790702*  |
| 박미경 | 840516*  |
| 김영균 | 810321*  |
| 지은경 | 800412*  |
| 임유진 | 830121*  |
| 서재진 | 851129*  |
| 이광훈 | 810913*  |
| 류민정 | 810819*  |
| 김진영 | 820606*  |
| 오유석 | 770912*  |
| 하나리 | 850109*  |
| 윤진욱 | 790402*  |
| 이동훈 | 831210*  |
| 박동진 | 851124*  |
| 김진경 | 830228*  |
| 조명훈 | 841214*  |
+--------+----------+
16 rows in set (0.00 sec)

• SUBSTRING(idnum,7) 에서 시작위치만 정하고 길이를 정하지 않은 경우는 시작 위치부터 끝까지 잘라낸다.