실전 쿼리는 대부분 문자열 처리에서 막힌다
숫자 다루기는 쉽습니다. 진짜 골치 아픈 건 문자열이죠. 공백이 섞인 이름, 대소문자가 뒤죽박죽인 이메일, 하이픈으로 이어 붙인 ID, 비슷한 듯 안 비슷한 자유 입력 필드까지. SQLite는 이런 상황 대부분을 애플리케이션 코드 없이 처리할 수 있도록 꼭 필요한 sqlite 문자열 함수들을 기본으로 제공합니다.
이 문서에서는 가장 자주 쓰게 될 함수들을 차근차근 살펴봅니다. 문자열 연결, 자르기, 검색, 치환, 트리밍, 포맷팅까지요.
sqlite 문자열 연결은 CONCAT이 아니라 ||
SQLite에는 CONCAT 함수가 없습니다. 문자열을 이어 붙일 때는 || 연산자를 사용합니다:
숫자나 다른 타입은 자동으로 텍스트로 변환됩니다. 다만 한 가지 함정이 있는데, 피연산자 중 하나라도 NULL이면 식 전체가 NULL이 되어 버린다는 점이죠. 이게 표준 SQL 동작이긴 한데, 처음 보면 다들 당황합니다:
COALESCE(col, '')나 COALESCE(col, 'default')로 감싸 두면, 값이 NULL이라고 해서 문자열 전체가 통째로 날아가는 일을 막을 수 있습니다.
LENGTH, UPPER, LOWER
이 세 함수는 정말 입에 달고 살게 됩니다:
LENGTH는 바이트가 아니라 문자 수를 돌려줍니다. 만약 진짜로 바이트 단위 길이가 필요하다면(드물긴 하지만 저장 공간 분석할 때 유용합니다) OCTET_LENGTH를 쓰세요. UPPER와 LOWER는 기본적으로 ASCII 알파벳만 변환합니다. 즉, 악센트가 붙은 문자는 ICU 확장을 로드하지 않는 한 그대로 통과됩니다.
SUBSTR로 문자열 자르기
SUBSTR(text, start, length)는 문자열에서 원하는 부분만 잘라냅니다. 인덱스는 0이 아니라 1부터 시작한다는 점에 주의하세요. 즉, 첫 글자는 1입니다:
몇 가지 기억해 둘 점이 있습니다.
- 세 번째 인자는 선택 사항입니다. 생략하면
start위치부터 문자열 끝까지 잘라서 돌려줍니다. start에 음수를 주면 문자열 끝에서부터 거꾸로 셉니다.start가 문자열 길이를 넘어가면 오류 없이 빈 문자열이 반환됩니다.
다른 DB에 손이 익은 분들을 위해 SUBSTRING도 동의어로 받아줍니다.
INSTR: 문자열에서 위치 찾기
INSTR(haystack, needle)는 haystack 안에서 needle이 처음 나타나는 위치를 1부터 시작하는 인덱스로 돌려주며, 찾지 못하면 0을 반환합니다.
방금 본 표현식이 바로 SQLite에서 "@ 앞부분만 잘라내기"를 처리하는 관용구입니다. INSTR로 구분자 위치를 찾고, SUBSTR로 잘라내는 식이죠. 이 조합은 앞으로도 자주 쓰게 됩니다. 한 가지 주의할 점은 INSTR이 매칭되는 게 없을 때 0을 돌려준다는 사실입니다. 이 0을 그대로 SUBSTR에 넘기면 엉뚱한 결과가 조용히 나오기 때문에, 자르기 전에 반드시 검사를 해야 합니다.
REPLACE 함수: 부분 문자열 한꺼번에 바꾸기
REPLACE(text, old, new)는 문자열 안에 등장하는 old를 모두 new로 바꿔 줍니다.
대소문자를 구분하며 정규식은 받지 않고, 그냥 리터럴 부분 문자열만 처리합니다. 좀 더 복잡한 변환이 필요하면 REPLACE를 여러 번 중첩해서 쓸 수도 있지만, 두세 번을 넘어가는 순간부터는 애플리케이션 단에서 처리하는 게 낫습니다.
sqlite trim 함수: TRIM, LTRIM, RTRIM
사용자가 입력한 데이터에는 양쪽 끝에 공백이 딸려 오는 경우가 많습니다. 이때 TRIM으로 깔끔하게 제거할 수 있습니다.
기본적으로는 공백 문자를 제거합니다. 두 번째 인자로 제거할 문자를 지정할 수 있는데, 이때 두 번째 인자에 들어간 문자들은 하나의 문자열 그 자체가 아니라 "제거할 문자 집합"으로 취급됩니다. 그래서 TRIM('xxxhelloxx', 'x')의 결과는 'hello'가 됩니다.
printf: 숫자와 문자열 포매팅
소수점 자릿수를 고정하거나, 숫자에 0을 채우거나, 16진수로 출력하는 등 형식이 정해진 문자열이 필요할 때는 printf(format이라고도 씁니다)를 쓰면 됩니다.
포맷 지정자는 C 언어 관례를 그대로 따릅니다. %d, %s, %f, %x는 물론이고 0이나 공백으로 패딩하는 것까지 가능하죠. || 연산자에 CAST를 잔뜩 붙여서 문자열을 조립하는 방식보다 훨씬 깔끔합니다.
LIKE vs GLOB: 패턴 매칭 비교
연산자는 두 개지만, 동작 방식은 완전히 다른 세계입니다.
LIKE는 SQL의 전통적인 와일드카드를 사용합니다. %는 임의의 문자열, _는 한 글자를 의미하고, ASCII 문자에 한해서는 대소문자를 구분하지 않습니다:
GLOB은 유닉스 셸 와일드카드를 그대로 사용합니다. *는 임의의 문자열, ?는 한 글자, [abc]는 문자 클래스를 뜻하며 대소문자를 구분합니다:
둘 중 뭘 쓸지 고를 때의 기준은 이렇다. 사람이 흔히 쓰는 "~로 시작", "~를 포함", "~로 끝남" 같은 매칭이 필요하면 LIKE. 대소문자 구분이 중요하거나 문자 클래스가 필요하면 GLOB. 둘 다 인덱스를 탈 수 있지만, 패턴이 앞쪽에 고정된 경우('%foo'가 아니라 'foo%')에만 가능하다. 와일드카드가 맨 앞에 오면 무조건 풀 스캔이다.
SQLite 문자열 자르기: SPLIT 함수는 없다
SQLite에는 SPLIT_STRING 같은 함수가 기본 제공되지 않는다. 실무에서 쓸 수 있는 우회 방법은 두 가지다:
구분자로 잘라서 여러 행으로 펼치고 싶다면, JSON 배열에 json_each를 거는 방식이나 재귀 CTE를 쓰는 게 가장 깔끔합니다. 두 방법 모두 뒤 챕터에서 자세히 다루니, 지금은 "단어 하나씩 다 뽑아줘"가 SQLite에서는 한 줄짜리 쿼리가 아니라는 점만 기억해 두세요.
실전 예제: 사용자 이름 정리하기
지금까지 배운 걸 한꺼번에 써먹어 봅시다. users 테이블에 표시 이름(display name)이 지저분하게 들어 있다고 해볼게요. 앞뒤로 공백이 붙어 있고, 대소문자가 뒤섞여 있고, "Dr. "나 "Mr. " 같은 호칭이 붙어 있는데 이걸 떼어내고 싶은 상황입니다.
표현식은 안쪽에서 바깥쪽으로 읽으면 됩니다. 먼저 바깥쪽 공백을 제거하고, 소문자로 바꾼 뒤, 직함을 떼어내고, 직함을 떼면서 앞쪽에 생긴 공백이 있을 수 있으니 다시 한 번 잘라냅니다. 각 단계는 함수 하나일 뿐이고, 복잡해 보이는 건 그저 함수가 겹겹이 쌓여서 그렇습니다. 이 스택이 서너 단계를 넘어가기 시작한다면, 생성 컬럼(Generated Column, "고급 기능" 장 참고)을 활용하거나 아예 데이터를 적재하는 시점에 정제 작업을 해두는 편이 낫다는 신호입니다.
핵심 정리
- 문자열 연결은
||로 합니다.NULL이 끼면 결과가 통째로 망가지니COALESCE로 막아주세요. SUBSTR과INSTR만 있으면 "찾아서 잘라내기" 작업의 대부분은 해결됩니다.REPLACE는 지정한 부분 문자열이 등장하는 모든 위치를 한꺼번에 치환합니다.TRIM계열 함수는 공백뿐 아니라 원하는 문자 집합을 직접 지정할 수 있습니다.- 형식화된 출력이 필요할 때는
printf가 정답입니다. - 대소문자를 구분하지 않는 SQL 와일드카드 매칭에는
LIKE, 대소문자를 구분하는 셸 스타일 패턴에는GLOB을 씁니다.
다음 장: 숫자 함수
문자열을 다뤘으니 자연스럽게 다음은 숫자 차례입니다. 반올림, 절댓값, 나눗셈에서 발생하는 함정, 그리고 최근 버전에서 SQLite에 새로 추가된 수학 함수들까지 살펴보겠습니다. 다음 페이지에서 이어집니다.
자주 묻는 질문
SQLite에서 문자열은 어떻게 이어붙이나요?
CONCAT 함수가 아니라 || 연산자를 씁니다. SQLite에는 기본적으로 CONCAT 함수가 없거든요. 예를 들어 'Hello, ' || name처럼 쓰면 두 문자열이 하나로 합쳐집니다. 단, 피연산자 중 하나라도 NULL이면 결과 전체가 NULL이 되니, 이런 동작을 원치 않을 때는 COALESCE로 감싸주세요.
SQLite에서 부분 문자열을 추출하려면 어떻게 하나요?
SUBSTR(text, start, length)을 쓰면 됩니다. SUBSTRING이라고 써도 동일하게 동작해요. 인덱스는 1부터 시작한다는 점에 주의하세요. 예를 들어 SUBSTR('hello', 1, 3)은 'hel'을 반환합니다. start에 음수를 넣으면 뒤에서부터 세고, length는 생략 가능한데 빼면 끝까지 잘라옵니다.
SQLite에 SPLIT_STRING 같은 함수가 있나요?
아쉽게도 SQLite에는 내장 split 함수가 없습니다. 대부분의 경우 INSTR와 SUBSTR을 조합하면 원하는 부분만 뽑아낼 수 있고, 구분자로 잘게 쪼개야 한다면 재귀 CTE를 쓰는 방법이 있어요. 자주 필요하다면 직접 splitter를 만드는 것보다 JSON 배열에 json_each를 적용하는 쪽이 훨씬 깔끔합니다.
SQLite에서 LIKE와 GLOB은 어떻게 다른가요?
LIKE는 기본적으로 ASCII 범위에서 대소문자를 구분하지 않으며 와일드카드로 %와 _를 사용합니다. 반면 GLOB은 대소문자를 구분하고, 유닉스 셸 스타일의 와일드카드(*, ?, [abc])를 씁니다. 대소문자 구분이 필요하거나 문자 클래스를 쓰고 싶다면 GLOB, 익숙한 SQL 스타일 매칭이 필요하다면 LIKE를 선택하면 됩니다.
