문자열 함수와 연산자

문자열 함수와 연산자 (String Functions and Operators)

문자열을 다루는 함수는 쿼리에서 가장 자주 쓰이는 기능 중 하나예요. PostgreSQL은 대소문자 변환, 부분 문자열 추출, 패딩, 정규식 검색·치환 등 정말 많은 함수를 제공하는데, 이 페이지에서 SQL 표준 문자열 함수와 그 밖의 문자열 함수들을 시그니처와 예시와 함께 정리해 볼게요.

출처: 공식문서

시작하기 전에

이 섹션의 함수·연산자는 character, character varying, text 타입의 값을 다뤄요. 특별한 언급이 없으면 함수·연산자는 text 타입을 받고 text 를 반환하도록 선언되며, character varying 인자를 그대로 받아들여요. character 타입 값은 함수나 연산자를 적용하기 전에 text 로 변환되는데, 이 과정에서 character 값의 뒤쪽 공백이 잘려요.

SQL은 인자를 쉼표가 아니라 키워드로 구분하는 일부 문자열 함수도 정의해요. PostgreSQL은 이 함수들의 일반 함수 호출 문법 버전도 함께 제공해요. 또 문자열 연결 연산자(||)는 입력 중 하나만 문자열 타입이면 비문자열 입력도 받아들여요. 다른 경우엔 text 로 명시적으로 강제 변환하면 돼요.

SQL 표준 문자열 함수·연산자

그 밖의 문자열 함수·연산자

연결·대소문자·문자 코드

• text ^@ text → boolean — 첫 문자열이 두 번째 문자열로 시작하면 true (starts_with() 와 동등). 예: 'alphabet' ^@ 'alph' → t
• ascii ( text ) → integer — 인자 첫 문자의 숫자 코드를 반환해요. UTF8에서는 유니코드 코드포인트, 다른 멀티바이트 인코딩에서는 ASCII 문자여야 해요. 예: ascii('x') → 120
• chr ( integer ) → text — 주어진 코드의 문자를 반환해요. UTF8에서는 코드포인트로 처리하고, 다른 멀티바이트 인코딩에서는 ASCII 문자여야 해요. chr(0) 은 텍스트 타입이 그 문자를 저장할 수 없어 금지됐어요. 예: chr(65) → A
• concat ( val1 "any" [, val2 "any" [, ...] ] ) → text — 모든 인자의 텍스트 표현을 연결해요. NULL 인자는 무시돼요. 예: concat('abcde', 2, NULL, 22) → abcde222
• concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text — 첫 인자를 구분자로 해서 나머지 인자를 연결해요. 첫 인자는 구분자 문자열이라 NULL이면 안 되고, 그 외 NULL 인자는 무시돼요. 예: concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
• initcap ( text ) → text — 각 단어의 첫 글자를 대문자, 나머지를 소문자로 변환해요. 단어는 비알파벳 문자로 구분되는 영숫자 시퀀스예요. 예: initcap('hi THOMAS') → Hi Thomas
• casefold ( text ) → text — 콜레이션에 따라 입력 문자열의 케이스 폴딩을 수행해요. 케이스 폴딩은 케이스 변환과 비슷하지만 목적이 달라요. 대소문자 무시 매칭을 돕는 것인 반면 케이스 변환은 특정 케이스 형태로 바꾸는 거예요. 서버 인코딩이 UTF8 일 때만 사용 가능해요. 보통 소문자로 바꾸지만 콜레이션에 따라 예외가 있을 수 있어요(어떤 문자는 소문자 변형이 둘 이상이거나 대문자로 폴딩되기도 해요). 문자열 길이를 바꿀 수도 있는데, 예를 들어 PG_UNICODE_FAST 콜레이션에서 ß(U+00DF)는 ss 로 폴딩돼요. libc 프로바이더는 케이스 폴딩을 지원하지 않아 lower 와 동일해요. |
• lower/upper — 앞서 다뤘어요.
• starts_with ( string text, prefix text ) → boolean — string 이 prefix 로 시작하면 true. 예: starts_with('alphabet', 'alph') → t

부분 문자열·자르기·채우기

• left ( string text, n integer ) → text — 처음 n 개 문자를 반환하고, n 이 음수면 마지막 |n|개를 뺀 나머지를 반환해요. 예: left('abcde', 2) → ab
• right ( string text, n integer ) → text — 마지막 n 개 문자를 반환하고, n 이 음수면 처음 |n|개를 뺀 나머지를 반환해요. 예: right('abcde', 2) → de
• length ( text ) → integer — 문자열의 문자 수를 반환해요. 예: length('jose') → 4
• substr ( string text, start integer [, count integer ] ) → text — start 번째 문자에서 시작해 지정 시 count 문자만큼 이어지는 부분 문자열을 추출해요. (substring(string from start for count) 와 동일.) 예: substr('alphabet', 3) → phabet, substr('alphabet', 3, 2) → ph
• reverse ( text ) → text — 문자열의 문자 순서를 뒤집어요. 예: reverse('abcde') → edcba
• repeat ( string text, number integer ) → text — string 을 number 번 반복해요. 예: repeat('Pg', 4) → PgPgPgPg
• replace ( string text, from text, to text ) → text — string 에서 부분 문자열 from 의 모든 출현을 to 로 교체해요. 예: replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef
• split_part ( string text, delimiter text, n integer ) → text — delimiter 출현에서 string 을 나누고 n 번째(1부터) 필드를 반환해요. n 이 음수면 마지막에서 |n|번째 필드예요. 예: split_part('abc~@~def~@~ghi', '~@~', 2) → def, split_part('abc,def,ghi,jkl', ',', -2) → ghi
• strpos ( string text, substring text ) → integer — string 안에서 substring 이 처음 나타나는 인덱스를 반환하고, 없으면 0이에요. (position(substring in string) 과 같지만 인자 순서가 바뀐 것에 주의.) 예: strpos('high', 'ig') → 2
• translate ( string text, from text, to text ) → text — string 에서 from 집합에 속한 각 문자를 to 집합의 대응 문자로 교체해요. from 이 to 보다 길면 from 의 남는 문자 출현은 삭제돼요. 예: translate('12345', '143', 'ax') → a2x5
• unistr ( text ) → text — 인자의 이스케이프된 유니코드 문자를 평가해요. 유니코드는 \XXXX(4자리 16진수), \+XXXXXX(6자리), \uXXXX(4자리), \UXXXXXXXX(8자리)로 지정할 수 있고, 백슬래시는 두 번 써서 지정해요. 서버가 UTF-8이 아니면 코드포인트를 실제 서버 인코딩으로 변환하고, 불가능하면 오류를 냅니다. 문자열 상수의 유니코드 이스케이프에 대한 (비표준) 대안이에요. 예: unistr('d\0061t\+000061') → data, unistr('d\u0061t\U00000061') → data

배열/테이블 변환

• string_to_array ( string text, delimiter text [, null_string text ] ) → text[] — delimiter 출현에서 string 을 나눠 text 배열로 만들어요. delimiter 가 NULL 이면 각 문자가 별도 요소가 되고, 빈 문자열이면 string 을 단일 필드로 취급해요. null_string 이 주어지고 NULL 이 아니면 그 문자열과 일치하는 필드를 NULL 로 바꿔요. 예: string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}
• string_to_table ( string text, delimiter text [, null_string text ] ) → setof text — 같은 규칙으로 나눈 결과를 행 집합으로 반환해요. delimiter 가 NULL 이면 각 문자가 별도 행, 빈 문자열이면 단일 필드, null_string 이 주어지면 일치 필드를 NULL 로 바꿔요. 예: string_to_table('xx~^~yy~^~zz', '~^~', 'yy') → xx, NULL, zz
• to_ascii ( string text [, encoding name|integer ] ) → text — string 을 다른 인코딩에서 ASCII로 변환해요(주로 억양 제거). encoding 을 생략하면 DB 인코딩을 가정해요. LATIN1, LATIN2, LATIN9, WIN1250 에서만 변환이 지원돼요. 예: to_ascii('Karél') → Karel

숫자 표현 변환

• to_bin ( integer|bigint ) → text — 숫자를 2의 보수 이진 표현으로 변환해요. 예: to_bin(2147483647) → 1111111111111111111111111111111
• to_hex ( integer|bigint ) → text — 숫자를 2의 보수 16진 표현으로 변환해요. 예: to_hex(2147483647) → 7fffffff
• to_oct ( integer|bigint ) → text — 숫자를 2의 보수 8진 표현으로 변환해요. 예: to_oct(2147483647) → 17777777777

인용·해시·파싱

• quote_ident ( text ) → text — SQL 문 문자열에서 식별자로 쓰기 좋게 적절히 따옴표를 붙인 문자열을 반환해요. 필요한 경우(비식별자 문자를 포함하거나 케이스 폴딩되면)에만 따옴표를 추가하고, 내장 따옴표는 제대로 두 배로 늘려요. 예: quote_ident('Foo bar') → "Foo bar"
• quote_literal ( text ) → text — SQL 문 문자열에서 문자열 리터럴로 쓰기 좋게 따옴표를 붙여요. 내장 작은따옴표·백슬래시는 제대로 두 배로 늘려요. NULL 입력엔 NULL을 돌려주니, 인자가 NULL일 수 있으면 quote_nullable 이 더 적합해요. 예: quote_literal(E'O\'Reilly') → 'O''Reilly'
• quote_literal ( anyelement ) → text — 값을 text로 변환한 뒤 리터럴로 인용해요. 예: quote_literal(42.5) → '42.5'
• quote_nullable ( text ) → text — 문자열 리터럴로 쓰기 좋게 인용하고, 인자가 NULL이면 NULL 을 반환해요. 예: quote_nullable(NULL) → NULL
• quote_nullable ( anyelement ) → text — 값을 text로 변환 후 인용하거나, NULL이면 NULL. 예: quote_nullable(42.5) → '42.5'
• md5 ( text ) → text — 인자의 MD5 해시를 16진수로 계산해요. 예: md5('abc') → 900150983cd24fb0d6963f7d28e17f72
• parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[] — qualified_identifier 를 식별자 배열로 나누고 개별 식별자의 인용을 제거해요. 기본적으로 마지막 식별자 뒤의 추가 문자는 오류로 보지만, 두 번째 인자가 false 면 무시해요. 함수 같은 객체의 이름 파싱에 유용해요. 길이 초과 식별자는 자르지 않고, 자르려면 결과를 name[] 로 캐스팅하세요. 예: parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}
• pg_client_encoding ( ) → name — 현재 클라이언트 인코딩 이름을 반환해요. 예: pg_client_encoding() → UTF8

정규식 함수

• regexp_count ( string text, pattern text [, start integer [, flags text ] ] ) → integer — string 에서 POSIX 정규식 pattern 이 일치하는 횟수를 반환해요. 예: regexp_count('123456789012', '\d\d\d', 2) → 3
• regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer — N 번째 일치가 발생하는 string 내 위치를 반환하고, 없으면 0이에요. 예: regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i') → 3, regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2) → 5
• regexp_like ( string text, pattern text [, flags text ] ) → boolean — string 안에 POSIX 정규식 pattern 일치가 있는지 확인해요. 예: regexp_like('Hello World', 'world$', 'i') → t
• regexp_match ( string text, pattern text [, flags text ] ) → text[] — string 에 대한 POSIX 정규식 pattern 의 첫 일치 안의 부분 문자열들을 반환해요. 예: regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}
• regexp_matches ( string text, pattern text [, flags text ] ) → setof text[] — 첫 일치 안의 부분 문자열들, g 플래그를 쓰면 모든 일치 안의 부분 문자열들을 반환해요. 예: regexp_matches('foobarbequebaz', 'ba.', 'g') → {bar}, {baz}
• regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text — POSIX 정규식 pattern 의 첫 일치(또는 g 플래그 시 모든 일치)인 부분 문자열을 교체해요. 예: regexp_replace('Thomas', '.[mN]a.', 'M') → ThM
• regexp_replace ( string text, pattern text, replacement text, start integer [, N integer [, flags text ] ] ) → text — string 의 start 번째 문자부터 검색해 N 번째 일치(또는 N 이 0이면 모든 일치)를 교체해요. N 생략 시 기본 1. 예: regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas
• regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[] — POSIX 정규식을 구분자로 써 string 을 나눠 배열을 만들어요. 예: regexp_split_to_array('hello world', '\s+') → {hello,world}
• regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text — 같은 방식으로 나눠 결과 집합을 만들어요. 예: regexp_split_to_table('hello world', '\s+') → hello, world
• regexp_substr ( string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text — POSIX 정규식 pattern 의 N 번째 출현과 일치하는 부분 문자열을 반환하고, 없으면 NULL. 예: regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i') → CDEF

format 함수

format 은 C의 sprintf 처럼 포맷 문자열에 따라 출력을 만드는 함수예요.

format(formatstr text [, formatarg "any" [, ...] ])

formatstr 은 결과를 어떻게 꾸밀지 지정하는 포맷 문자열이에요. 포맷 지정자(placeholder)가 있는 곳을 제외하면 문자열의 텍스트가 그대로 결과에 복사돼요. 각 formatarg 인자는 자기 타입의 일반 출력 규칙에 따라 text로 변환된 뒤, 지정자에 따라 포맷되어 결과 문자열에 들어가요.

포맷 지정자는 % 문자로 시작하며 %[position][flags][width]type 형태예요.

• position(선택) — n$ 형태로, formatstr 이후 n 번째 인자를 가리켜요. 생략하면 순서대로 다음 인자를 써요.
• flags(선택) — 현재 유일하게 마이너스(-)를 지원하며, 지정자 출력을 왼쪽 정렬해요. width 가 지정돼야 효과가 있어요.
• width(선택) — 출력에 쓸 최소 문자 수예요. 왼쪽/오른쪽에 공백을 채워 맞추고, 너무 작은 폭은 잘리지 않고 무시돼요. 양의 정수, 다음 함수 인자를 폭으로 쓰는 별표(*), *n$ 형태로 n 번째 인자를 폭으로 쓰는 방법이 있어요. 폭이 함수 인자에서 오면 그 인자를 지정자 값 인자보다 먼저 소비하고, 폭 인자가 음수면 abs(width) 길이의 필드 안에서 왼쪽 정렬해요.
• type(필수) — 변환 타입. s 는 값을 단순 문자열로(널은 빈 문자열), I 는 SQL 식별자로(필요 시 큰따옴표, quote_ident 와 동등, 값이 널이면 오류), L 은 SQL 리터럴로 인용(널은 NULL 문자열로 표시, quote_nullable 과 동등)해요. 특수 시퀀스 %% 는 리터럴 % 를 출력해요.

예시를 볼게요.

SELECT format('Hello %s', 'World');            -- Hello World
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');  -- Testing one, two, three, %
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');  -- INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('|%10s|', 'foo');                -- |       foo|
SELECT format('|%-10s|', 'foo');               -- |foo       |
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');  -- Testing three, two, one

C의 sprintf 와 달리 PostgreSQL의 format 은 같은 포맷 문자열 안에서 position 필드를 가진 지정자와 아닌 지정자를 섞을 수 있고, 모든 인자를 포맷 문자열에 쓰지 않아도 돼요.

%I 와 %L 지정자는 특히 동적 SQL 문을 안전하게 만들 때 유용해요.

concat, concat_ws, format 함수는 가변 인자(variadic)라서 VARIADIC 키워드로 표시한 배열을 넘겨 값을 전달할 수도 있어요. 배열 요소는 별도의 일반 인자처럼 취급돼요. 가변 배열 인자가 NULL이면 concat·concat_ws 는 NULL을 반환하지만, format 은 NULL을 빈 배열로 봐요.

더 알아보기 (Learn more)

• 패턴 매칭(POSIX 정규식 등)
• 집계 함수 string_agg
• 문자열과 bytea 타입 변환