⛏️/Mysql | Oracle

[SQL] 10. 문자 함수

defyuil 2023. 8. 9. 13:00
ASCII(아스키코드) 문자의 아스키 코드값을 반환해 주는 함수
CHAR(숫자) 숫자의 아스키 코드값을 반환해 주는 함수
예제 select ascii('A'), char(65);

※ MySQL에서는 단순한 함수 겨로가나 계산 결과를 출력하는 경우 즉, 접근해야 하는 테이블이 없는 출력 구문인 경우 select절만 작성할 수 있음

※ 출력 결과에 BLOB로 표시되는 경우(Workbench의 버그) 오른쪽 마우스 클릭 후 [Open Calue in Viewer] 선택 후 [Text] 탭에 들어가면 정상적인 출력 결과를 확인할 수 있음

 

 

LENGTH(문자열) 문자열의 byte 수를 반환해 주는 함수
BIT_LENGTH(문자열) 할당된 bit 크기 또는 문자 크기를 반환해 주는 함수
CHAR_LENGTH(문자열) 문자의 개수를 반환해 주는 함수
예제 select length('abc'), bit_length('abc'), char_length('abc')
select length('가나다), bit_length('가나다'), char_length('가나다')

 

 

단일행 함수

  • 개별 행단위로 입력받은 값에 대한 결과를 돌려주는 종류의 함수

문자 함수

  • 문자 데이터를 주제로 연산하는 함수의 종류
  • 리턴값은 주로 문자열이나 숫자데이터이다.

 

ASCII 아스키코드 → 숫자 / CHAR  숫자 → 아스키코드

SELECT ASCII('A'), CHAR(65);

ASCII('A')|CHAR(65)|

----------+--------+

        65|A       |

 

 |

 

LENGTH / BIT_LENGTH / CHAR_LENGTH

  • LENGTH : 입력받은 문자열 값의 byte를 돌려준다.
  • BIT_LENGTH : 입력받은 문자열 값의 bit를 돌려준다. 1byte = 8bit
  • CHAR_LENGTH : 입력받은 문자열의 문자수를 돌려준다.

 

  • 영문자의 경우 1글자 표현에 1byte를 사용한다.
SELECT LENGTH('ABC'), BIT_LENGTH('ABC'), CHAR_LENGTH('ABC');

LENGTH('ABC')|BIT_LENGTH('ABC')|CHAR_LENGTH('ABC')|

-------------+-----------------+------------------+

            3|               24|                 3|

 

 

  • 한글의 경우 1글자 표현에 2~3byte를 사용하기 때문에 글자수와 byte의 값이 서로 다르다.
SELECT LENGTH('ㄱㄴㄷ'), BIT_LENGTH('ㄱㄴㄷ'), CHAR_LENGTH('ㄱㄴㄷ');

LENGTH('ㄱㄴㄷ')|BIT_LENGTH('ㄱㄴㄷ')|CHAR_LENGTH('ㄱㄴㄷ')|

----------------+--------------------+---------------------+

               9|                  72|                    3|

 

 

CONCAT / CONCAT_WS

  • CONCAT : 입력받은 문자열 값들을 모두 순서대로 합친 결과값을 출력한다.
  • CONCAT_WS : 입력받은 문자열들을 순서대로 합치나 합쳐지는 문자열들 사이사이에 설정한 구분자를 포함하여 결과를 출력해준다.
SELECT employee_id,
CONCAT(first_name, last_name) AS "이름"
FROM employees;

 

 

SELECT first_name, last_name,
CONCAT(first_name,' ',last_name) AS "이름"
FROM employees;

 

 

SELECT CONCAT_WS('/', '2025', '01', '01');

 

 

SELECT CONCAT_WS('--', last_name, job_id, salary)
FROM employees;

 

 

INSTR

  • 기준 문자열에서 특정 문자열의 자리값을 찾아 숫자로 결과를 돌려주는 함수
  • 기준 문자열 '하나둘셋'에서 문자열 '둘'의 자리값을 연산하는 내용이다.
SELECT INSTR('하나둘셋', '둘');

 

 

 

  • mysql에서 문자열의 자리값은 왼쪽에서부터 자리값 1로 시작을 한다.

  • 0자리값은 존재하지 않는 값이다. INSTR이 0을 돌려주는 경우는 기준 문자열에서 찾는 문자열이 존재하지 않는 경우일 때 이다.
SELECT last_name, INSTR(last_name, 'a')
FROM employees;

 

 

 

  • last_name컬럼의 값에서 'a'가 1번의 자리에 있는 행만 출력한다.
SELECT last_name
FROM employees
WHERE INSTR(last_name, 'a') = 1;

 

 

문자열 자리값

  • 정방향 (왼 → 오) : 양수의 정수값
  • 역방향 (오 → 왼) : 음수의 정수값

 

 

 

LOWER / UPPER

  • LOWER : 입력받은 문자열을 일괄 소문자로 변환하여 출력하는 함수
  • UPPER : 입력받은 문자열을 일괄 대문자로 변환하여 출력하는 함수
SELECT LOWER('Abc'), UPPER('Abc');

 

 

 

SELECT employee_id, UPPER(last_name) AS "L-name", LOWER(job_id) AS "Job",
phone_number, LOWER(email) AS "E-mail"
FROM employees;

 

 

SELECT CONCAT('The job id for ', UPPER(last_name), ' is ', LOWER(job_id))
FROM employees;

 

 

 

LEFT / RIGHT

  • LEFT : 입력 받은 문자열에서 왼쪽 끝부터 지정된 자리수의 문자를 추출하는 함수
  • RIGHT : 입력 받은 문자열에서 오른쪽 끝부터 지정된 자리수의 문자를 추출하는 함수
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);

 

 

SELECT CONCAT(first_name, last_name) "FULL NAME",
LEFT(CONCAT(first_name, last_name), LENGTH(first_name)) first_name,
RIGHT(CONCAT(first_name, last_name), LENGTH(last_name)) last_name
FROM employees;

 

 

SELECT LPAD('ABC', 5, '#'), RPAD('ABC', 5, '#');

 

 

 

SELECT LPAD(last_name, 20, '_') AS "L-name", RPAD(first_name, 20, '_') AS "F-name"
FROM employees;

 

 

 

SELECT CONCAT('사원 ', LPAD(last_name, 11, ' '), '(은/는) 월에 ',
LPAD(salary, 8, ' '), ' 만큼을 번다.') "월급"
FROM employees;

 

 

연습문제 풀이

SELECT last_name, LPAD(salary, 15, '$') salary
FROM employees;

 

 

SELECT CONCAT(RPAD(last_name, 13, ' '), LPAD('', salary / 1000, '*')) EMPLOYEES_AND_THEIR_SALARIES
FROM employees;

 

 

LTRIM / RTRIM

  • LTRIM|RTRIM(문자열)
  • 입력받은 문자열에서 각 함수의 방향에 있는 공백문자를 제거해주는 함수.
  • 공백이 아닌 문자열이 있는 경우 더 이상 제거를 하지 않는다.
SELECT LTRIM(' SQL 문법 ') L, RTRIM(' SQL 문법 ') R;

 

 

TRIM

  • 입력받은 문자열의 공백을 제거하거나 특정 문자열을 지정하여 원하는 방향에서 제거한 결과를 만들어주는 함수.
  • TRIM([leading|trailing|both] '제거문자' FROM '전체문자')
  • TRIM에 문자열값만 입력하는 경우 양쪽 방향에서 공백을 제거한다.
  • 필요한 경우 제거할 문자를 지정하고 제거를 시작할 방향의 설정도 가능하다.
SELECT TRIM(' SQL 문법 ') B, TRIM(BOTH '_' FROM '___ SQL ___') T;

 

 

 

REPLACE

  • 기준 문자열에서 특정 문자를 다른 문자로 대체하여 결과를 출력해주는 함수
  • REPLACE(기준문자열, 기존문자열, 대체문자열)
SELECT employee_id, phone_number, REPLACE(phone_number, '.', '-') AS "전화번호"
FROM employees;

 

 

SPACE

  • 입력한 수만큼의 공백문자열을 돌려주는 함수
SELECT CONCAT('MySQL', SPACE(10), 'DBMS');

 

 

 

SUBSTR|SUBSTRING

  • 문자열의 일부를 자리값과 글자수를 사용하여 추출해주는 함수
  • SUBSTR|SUBSTRING(전체문자열, 시작자리값, 칸수)
SELECT SUBSTR('대한민국만세', 3, 2);

 

 



SELECT last_name, SUBSTR(last_name, 2, 3), SUBSTRING(last_name, 2, 3)
FROM employees;

 

 

 

  • SUBSTR 함수는 음수의 자리값을 사용하여 오른쪽부터 자리값을 세는 것이 가능하다.
  • 아래는 오른쪽에서부터 두번째 자리에서 2칸을 추출할 때의 값이 'en'과 일치한 값을 가진 행을 출력하는 내용이다.
SELECT last_name, SUBSTR(last_name, -2, 2)
FROM employees
WHERE SUBSTR(last_name, -2, 2) = 'en';

 

 

연습문제 풀이

SELECT last_name Name, LENGTH(last_name) LENGTH
FROM employees
WHERE SUBSTR(last_name, 1, 1) IN ('J', 'A', 'M')
ORDER BY 1;

SELECT last_name Name, LENGTH(last_name) LENGTH
FROM employees
WHERE INSTR(last_name, 'A') = 1
OR INSTR(last_name, 'J') = 1
OR INSTR(last_name, 'M') = 1
ORDER BY 1;