[MySQL] 문자열 검색 함수: INSTR, LOCATE, POSITION 비교

특정 문자열 안에서 어떤 단어나 문자가 어디에 위치해 있는지 확인하고 싶을 때 쓰는 함수들이 있다.
그 대표적인 함수가 INSTR, LOCATE, POSITION이다.
각 함수의 동작 원리와 차이점, 실무 활용법까지 확실하게 정리해보자.

🔍 INSTR(str, substr): 위치 반환

문법

INSTR(전체문자열, 찾을문자열)

• 찾은 위치(index)를 반환한다.
• 위치는 1부터 시작, 없으면 0 반환

예제

SELECT INSTR('developer', 'e');     -- 결과: 2 (첫 e 위치)
SELECT INSTR('developer', 'lop');   -- 결과: 5
SELECT INSTR('developer', 'z');     -- 결과: 0 (없음)

실무 팁

-- 이메일에 '@'이 포함된 위치 찾기
SELECT INSTR(email, '@') FROM users;

INSTR은 간단하고 가독성이 좋아서 많은 개발자들이 선호하는 방식이다.

반응형

🔸 LOCATE(substr, str [, start]): 시작위치 지정 가능

문법

LOCATE(찾을문자열, 전체문자열 [, 시작위치])

• INSTR과 순서가 반대
• start 인자를 지정하면, 그 위치부터 검색 시작

예제

SELECT LOCATE('e', 'developer');       -- 결과: 2
SELECT LOCATE('e', 'developer', 3);    -- 결과: 7 (앞부분 무시)

실무 팁

-- 이메일의 도메인 시작 위치 찾기
SELECT LOCATE('@', email) FROM users;

주의: LOCATE('e', 'developer')와 INSTR('developer', 'e')는 같은 결과를 낸다.
하지만 LOCATE는 **검색 시작 위치(start pos)**를 지정할 수 있어서 더 유연하다.

🔹 POSITION(substr IN str): 표준 SQL 스타일

문법

POSITION(찾을문자열 IN 전체문자열)

예제

SELECT POSITION('e' IN 'developer');   -- 결과: 2
SELECT POSITION('x' IN 'developer');   -- 결과: 0

• SQL 표준문법
• 내부적으로는 INSTR과 동일하게 동작
• MySQL 전용이 아닌 다양한 DBMS에서 호환성 고려 시 사용

실무 팁

-- 텍스트 내 키워드 위치 확인 (DB 이식성 고려)
SELECT POSITION('ERROR' IN log_message) FROM logs;

반응형

🧠 세 함수 비교 요약

함수명	사용법 예시	특징 및 차이점
INSTR	INSTR('abcde', 'c')	직관적인 순서, NULL 무시, 실무에서 흔히 사용
LOCATE	LOCATE('c', 'abcde', 2)	검색 시작 위치 지정 가능, INSTR보다 유연
POSITION	POSITION('c' IN 'abcde')	SQL 표준 스타일, 호환성 고려 시 추천

⚠️ 공통 주의사항

• 모든 함수는 1부터 시작하는 위치 반환
• 찾는 문자열이 없으면 0 반환
• 대소문자 구분은 기본적으로 하지 않음, 다만 컬레이션에 따라 다름
• NULL이 하나라도 들어가면 결과는 NULL

SELECT INSTR(NULL, 'a'); -- 결과: NULL
SELECT LOCATE('a', NULL); -- 결과: NULL

🛠️ 실무 활용 예제

-- 전화번호에 '-' 포함 여부 확인
SELECT IF(INSTR(phone, '-') > 0, 'O', 'X') AS dash_exists FROM users;

-- 로그 문자열 중 특정 키워드가 포함된 행 필터링
SELECT * FROM logs
WHERE POSITION('timeout' IN message) > 0;

-- 3번째 이후에 나타나는 'a' 위치
SELECT LOCATE('a', comment, 3) FROM feedback;

 

🔍 마무리 정리

• 빠르게 문자열 위치만 확인할 땐 INSTR()
• 검색 시작 위치를 지정하고 싶다면 LOCATE()
• SQL 호환성과 이식성을 고려하면 POSITION()
• 기본값 처리, 대소문자 구분, NULL 체크는 항상 조심하자

🔗 참고 공식 문서
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html