자주 사용하는 SQL 문자열 함수를 정리 해 보았습니다. 

날짜의 경우 년도와 월만 표시하거나 특수 문제를 제거 변경할 때 사용하기도 했었습니다.

문자 길이와 대소문자 변환 함수

문자열 길이를 파악하고 형식을 맞추는 가장 기초적인 처리

LENGTH()

LENGTH 함수는 문자열이 차지하는 바이트 수를 반환합니다. UTF-8 환경에서는 영문자와 숫자는 1바이트, 한글은 3바이트로 계산되므로, 저장 용량이나 바이트 기준 길이를 확인할 때 유용합니다.

SELECT LENGTH('ABC');   -- 3
SELECT LENGTH('가나');  -- 6 (UTF-8 기준)

CHAR_LENGTH()

CHAR_LENGTH 함수는 실제 글자 수를 반환합니다. 한글처럼 멀티바이트 문자를 다룰 때는 바이트 수보다는 글자 수가 중요한 경우가 많기 때문에, 화면 출력 길이 계산이나 내용 요약 등에 자주 사용됩니다.

SELECT CHAR_LENGTH('ABC');  -- 3
SELECT CHAR_LENGTH('가나'); -- 2

UPPER()

UPPER 함수는 문자열을 모두 대문자로 변환합니다. 코드, 약어, 국가코드 등 대문자로 통일해야 하는 값이나, 검색 조건을 대소문자 구분 없이 처리하고 싶을 때 기준을 맞추는 용도로 활용합니다.

SELECT UPPER('sigoni');  -- SIGONI

LOWER()

LOWER 함수는 문자열을 소문자로 변환합니다. 이메일 주소, URL, 태그 등 소문자로 통일해 관리하는 데이터에 많이 사용되며, WHERE 절에서 LOWER(col) = 'value' 형태로 비교하는 패턴도 자주 등장합니다.

SELECT LOWER('SIGONI');  -- sigoni

일부 문자열만 잘라 쓰는 함수

앞·뒤·중간에서 필요한 구간만 추출하기

SUBSTRING()

SUBSTRING은 문자열의 특정 위치에서 지정한 길이만큼 잘라냅니다. 주민번호 일부, 날짜 문자열에서 연·월만 추출하는 등 다양한 파싱 작업에서 기본 도구로 쓰입니다.

SELECT SUBSTRING('abcdef', 2, 3);  -- bcd

MID()

MID 함수는 SUBSTRING과 같은 기능을 하는 별칭 함수입니다. 기존 습관이나 코드 스타일에 따라 MID를 사용하는 프로젝트도 있기 때문에, 둘이 동일하다는 점만 알고 있으면 충분합니다.

SELECT MID('abcdef', 3, 2);  -- cd

LEFT()

LEFT 함수는 문자열의 왼쪽에서부터 지정한 길이만큼 잘라냅니다. 연도만 따로 추출하거나, 코드의 앞부분만 비교할 때 자주 사용됩니다.

SELECT LEFT('20251124', 4);  -- 2025

RIGHT()

RIGHT 함수는 문자열의 오른쪽에서부터 지정한 길이만큼 잘라냅니다. 전화번호 뒷자리, 파일 확장자, 코드 끝부분 등을 가져올 때 매우 편리합니다.

SELECT RIGHT('01012345678', 4);  -- 5678

문자 위치 검색 함수

문자열 안에서 특정 패턴이 어디 있는지 찾기

INSTR()

INSTR 함수는 문자열 안에 특정 패턴이 등장하는 위치를 반환합니다. 0보다 큰 값이 나오면 문자열이 포함된 것이므로, 간단한 포함 여부 체크에도 활용할 수 있습니다.

SELECT INSTR('hello world', 'world');  -- 7

LOCATE()

LOCATE 함수 역시 문자열 위치를 찾지만, 인자 순서가 LOCATE(찾을문자열, 대상문자열) 형태입니다. 사용 방식은 INSTR와 거의 동일하며, 프로젝트 스타일에 따라 둘 중 하나를 선택해서 사용합니다.

SELECT LOCATE('on', 'sigoni');  -- 3

POSITION()

POSITION 함수는 표준 SQL 스타일로, POSITION(찾을문자열 IN 대상문자열) 형태를 사용합니다. 다른 DBMS와의 호환성이 중요할 때 POSITION 형식을 선호하기도 합니다.

SELECT POSITION('ERP' IN 'sigoniERPservice');  -- 7

문자열 결합 함수

여러 값을 하나의 문자열로 합치기

CONCAT()

CONCAT 함수는 여러 문자열을 하나로 이어붙입니다. 고정 포맷 코드 생성, 메시지 조합, 로그 문자열 만들기 등 거의 모든 종류의 SQL에서 반복적으로 등장합니다.

SELECT CONCAT('USER_', '0001');   -- USER_0001

CONCAT_WS()

CONCAT_WS는 첫 번째 인자를 구분자로 사용해 나머지 인자들을 결합합니다. 날짜를 yyyy-mm-dd 형식으로 만들거나, 여러 값을 쉼표로 이어 붙이는 작업 등을 간단하게 처리할 수 있습니다.

SELECT CONCAT_WS('-', '2025', '11', '24');  -- 2025-11-24

공백 제거와 문자열 정리 함수

양쪽 공백 제거로 데이터 정제하기

TRIM()

TRIM 함수는 문자열 앞뒤의 공백을 제거합니다. 엑셀에서 복사해 온 값이나 외부 시스템에서 전달된 데이터에는 보이지 않는 공백이 자주 포함되므로, 저장 전 또는 비교 전 TRIM을 한 번 적용해 주는 것이 안전합니다.

SELECT TRIM('   abc   ');  -- abc

LTRIM()

LTRIM은 왼쪽 공백만 제거합니다. 특정 형식의 데이터에서 앞부분에만 공백이 들어오는 경우에 선택적으로 사용할 수 있습니다.

SELECT LTRIM('   abc');    -- abc

RTRIM()

RTRIM은 오른쪽 공백만 제거합니다. 고정 길이 텍스트 컬럼에서 뒷부분에 공백이 채워지는 상황에서 정리용으로 쓰입니다.

SELECT RTRIM('abc   ');    -- abc

문자 치환과 패딩 함수

일부 문자 교체와 원하는 길이로 맞추기

REPLACE()

REPLACE 함수는 문자열 전체에서 특정 패턴을 다른 문자열로 교체합니다. 전화번호에서 하이픈 제거, 경로 문자열 수정, 특정 단어 치환 등 다양한 전처리 작업에 유용합니다.

SELECT REPLACE('010-1234-5678', '-', '');  -- 01012345678

INSERT()

INSERT 함수는 지정한 위치에서 일정 길이만큼을 새로운 문자열로 교체합니다. 문자열 길이를 유지한 채 일부만 바꾸고 싶을 때 사용합니다.

SELECT INSERT('abcdef', 3, 2, 'XY');  -- abXYef

LPAD()

LPAD 함수는 문자열의 왼쪽을 특정 문자로 채워 지정한 길이로 맞춥니다. 숫자 코드, 문서 번호, 고객 번호를 4자리·6자리 등 일정 길이로 표현할 때 자주 사용됩니다.

SELECT LPAD('123', 5, '0');  -- 00123

RPAD()

RPAD 함수는 문자열의 오른쪽을 지정한 문자로 채웁니다. 화면 출력에서 열 간격을 맞추거나, 고정 길이 형식의 파일을 만들 때 쓰일 수 있습니다.

SELECT RPAD('ABC', 5, '*');  -- ABC**

반복·비교·세트 관련 함수

반복 문자, 문자열 비교, CSV 형태 처리

REPEAT()

REPEAT 함수는 지정한 문자열을 원하는 횟수만큼 반복해서 반환합니다. 테스트용 더미 데이터 생성이나 간단한 패턴 문자열을 만들 때 활용할 수 있습니다.

SELECT REPEAT('A', 5);  -- AAAAA

STRCMP()

STRCMP는 두 문자열을 비교해 같으면 0, 앞 문자열이 작으면 -1, 크면 1을 반환합니다. 정렬 기준을 커스텀하게 만들거나 조건 분기를 세밀하게 처리할 때 사용할 수 있습니다.

SELECT STRCMP('abc', 'abd');  -- -1

FIND_IN_SET()

FIND_IN_SET은 쉼표로 구분된 문자열 목록에서 특정 값이 몇 번째에 있는지 반환합니다. 다만 CSV 형태 문자열 저장은 구조적으로는 비추천 패턴이므로, 불가피한 경우에만 쓰는 것이 좋습니다.

SELECT FIND_IN_SET('B', 'A,B,C');  -- 2

FIELD()

FIELD 함수는 여러 값 중 특정 값의 위치(순서)를 알려줍니다. 우선순위를 직접 정의해 정렬 순서를 맞추고 싶을 때, ORDER BY FIELD(col, 'HIGH','MID','LOW')처럼 사용하는 패턴이 자주 쓰입니다.

SELECT FIELD('B', 'A', 'B', 'C');  -- 2

형 변환과 역순 처리 함수

자료형 변환과 문자열 뒤집기

CAST()

CAST 함수는 숫자를 문자열로, 문자열을 숫자로 등 타입을 명시적으로 변환합니다. 숫자와 문자를 결합할 때, 또는 문자열 형태로 저장된 숫자를 계산에 사용할 때 자주 등장합니다.

SELECT CAST(123 AS CHAR);  -- '123'

CONVERT()

CONVERT 함수는 타입 변환뿐 아니라 문자셋 변환에도 쓰입니다. 이론적으로는 인코딩을 바꿀 때 활용할 수 있지만, 일반적인 웹 개발에서는 주로 타입 변환 용도로 이해해도 충분합니다.

SELECT CONVERT('ABC' USING utf8mb4);

REVERSE()

REVERSE 함수는 문자열을 거꾸로 뒤집어서 반환합니다. 직접적인 실무 사용 빈도는 높지 않지만, 간단한 난독화나 테스트, 특이한 출력 형식을 만들 때 재미있게 활용할 수 있습니다.

SELECT REVERSE('ABC');  -- CBA

JSON 문자열 처리 함수

JSON 컬럼에서 문자 값 추출·정리·수정

JSON_EXTRACT()

JSON_EXTRACT 함수는 JSON 형식 데이터에서 특정 경로에 있는 값을 추출합니다. 결과는 JSON 형식 그대로 반환되므로, 따옴표가 포함된 문자열처럼 보일 수 있다는 점을 함께 기억해야 합니다.

SELECT JSON_EXTRACT('{"name":"sigoni"}', '$.name');  -- "sigoni"

JSON_UNQUOTE()

JSON_UNQUOTE는 JSON_EXTRACT로 가져온 값에서 양쪽 따옴표를 제거해 일반 문자열처럼 사용할 수 있게 만들어 줍니다. WHERE 절이나 LIKE 조건 등에 바로 쓰고 싶을 때 조합해서 사용합니다.

SELECT JSON_UNQUOTE(
    JSON_EXTRACT('{"name":"sigoni"}', '$.name')
);  -- sigoni

JSON_SET()

JSON_SET 함수는 JSON 데이터 안의 특정 경로에 있는 값을 새 값으로 바꿉니다. 옵션 값, 설정 정보, 복잡한 구조 데이터를 하나의 JSON 컬럼에 저장하는 설계에서 자주 사용하는 패턴입니다.

SELECT JSON_SET('{"name":"sigoni"}', '$.name', '시고니');
-- {"name":"시고니"}