[Mysql] 자주 쓰이는 문자함수 모음

단일행 함수

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

 

문자 함수

• 문자 데이터를 주제로 연산하는 함수의 종류

• 리턴값은 주로 문자열이나 숫자데이터이다.

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

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

⇒ 직접 리터럴을 지정해줬기 때문에 FROM절을 생략해도 나옴

 

length, bit_length, char_length

• LENGTH(문자열) : 문자열의 byte 수를 반환해주는 함수
• BIT_LENGTH : 입력받은 문자열 값의 bit를 돌려준다. 1byte = 8bit
• CHAR_LENGTH : 입력받은 문자열의 문자수를 돌려준다.

⇒ length :영어는 한글자당 1byte인 것을 알 수 있다.

bit_length : 3*8bit=24bit

char_length : 3글자

⇒ length :한글은 한글자당 3byte인 것을 알 수 있다.

bit_length : 9*8bit=72bit

char_length : 3글자

 

CONCAT / CONCAT_WS

• CONCAT : 입력받은 문자열 값들을 모두 순서대로 합친 결과값을 출력한다.
• CONCAT_WS : 입력받은 문자열들을 순서대로 합치나 합쳐지는 문자열들 사이사이에 설정한 구분자를 포함하여 결과를 출력해준다.
• 아래와 같이 테이블의 특정 컬럼의 값을 입력받아 행마다 가진 컬럼의 값을 조합하여 결과로 만들 수 있다.

CONCAT 함수 : 문자열을 연결해주는 함수

CONCAT(문자열1, 문자열2)

• 경우에 따라 컬럼의 데이터가 아닌 문장 양식과 같이 고정적인 위치, 순서에 값이 조합되어야 하는 경우 리터럴 값을 직접 작성하여 테이블의 데이터와 상관없이 내용을 조합할 수도 있다.

CONCAT_WS 문자함수 : 구분자와 함께 문자열을 연결해주는 함수

CONCAT_WS(구분자, 문자열1, 문자열2)

INSTR : 기준 문자열에서 부분 문자열의 시작 위치값을 반환해 주는 함수

즉, 기준 문자열에서 특정 문자열의 자리값을 찾아 숫자로 결과를 돌려주는 함수

INSTR(기준 문자열, 부분 문자열)

문자열 자리값

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

UPPER / LOWER

• LOWER : 입력받은 문자열을 일괄 소문자로 변환하여 출력하는 함수
• UPPER : 입력받은 문자열을 일괄 대문자로 변환하여 출력하는 함수

SELECT lower('Abc'), upper('abc');

• 입력받은 문자들이 대문자, 소문자로 변환됨

 

역방향조회

SELECT last_name
FROM employees
WHERE instr(last_name, 'en') = length(last_name) -1;

• INSTR(last_name, 'n')은 last_name 컬럼 데이터에서 'n'이 포함된 위치값을 돌려준다.
• LENGTH(last_name)은 last_name의 문자열 길이값을 돌려준다.

• 이 때 문자열의 길이 값은 문자열의 자리값으로 친다면 마지막 위치의 자리값과 동일하다.
• INSTR(last_name, 'n') = LENGTH(last_name) 따라서, 이 조건식은 'n'의 자리값이 last_name의 마지막 자리에 위치인 행만 출력하도록하는 조건식으로 동작하게 된다.

LEFT / RIGHT

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

LPAD / RPAD

• LPAD|RPAD(문자열, 길이, 채울 문자)

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

LTRIM / RTRIM (문자열)

• 입력받은 문자열에서 각 함수의 방향에 있는 공백문자를 제거해주는 함수.
• 공백이 아닌 문자열이 있는 경우 더 이상 제거를 하지 않는다.

SELECT LTRIM(' SQL 문법 ') L, RTRIM(' SQL 문법 ') R;

TRIM(문자열)

• 입력받은 문자열의 공백을 제거하거나 특정 문자열을 지정하여 원하는 방향에서 제거한 결과를 만들어주는 함수
• TRIM에 문자열값만 입력하는 경우 양쪽 방향에서 공백을 제거한다.
• 필요한 경우 제거할 문자를 지정하고 제거를 시작할 방향의 설정도 가능하다.

TRIM([leading|trailing|both] '제거문자' FROM '전체문자')

leading : 앞 trailing : 뒤 both : 양쪽

REPLACE(문자열, 기존문자열, 바꿀문자열)

• 기준 문자열에서 특정 문자를 다른 문자로 대체하여 결과를 출력해주는 함수
• REPLACE(문자열, 기존문자열, 대체문자열)

SELECT employee_id, phone_number, replace(phone_number,'.','-') AS "전화번호"
FROM employees;

SPACE(길이)

• 입력한 수만큼의 공백문자열을 돌려주는 함수

SELECT CONCAT('MySQL', SPACE(10), 'DBMS');

SUBSTR | SUBSTRING

• 문자열의 일부를 자리값과 글자수를 사용하여 추출해주는 함수
• SUBSTR|SUBSTRING(전체문자열, 시작자리값, 칸수)

SUBSTR(문자열, 시작위치, 길이)

SELECT SUBSTR('대한민국만세', 3, 2);

SUBSTRING

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