반응형
해당 글에서는 데이터베이스 PostgreSQL을 기반으로 문자열 함수(String Function)에 대해 알아봅니다.
1) 문자열 함수(String Function)
💡 문자열 함수(String Function)
- 문자열을 조작하고 변환하는 데 사용되는 함수들을 의미합니다.
PostgreSQL String Functions
This page provides you with the most commonly used PostgreSQL string functions that allow you to manipulate string data effectively. FunctionDescriptionExampleResultASCIIReturn the ASCII code value of a character or Unicode code point of a UTF8 characterAS
www.postgresqltutorial.com
2) 문자열 함수 구조
💡 문자열 함수 구조는 일반적으로 'FUNCTION_NAME'과 '매개변수로 1개 이상의 값'의 형태로 구성되어 있습니다.
💡 문자열 함수 구조 설명
- FUNCTION_NAME : 사용할 문자열 함수의 이름입니다.
- arg1, arg2, …, argN: 함수에 전달할 값 또는 변수입니다.
FUNCTION_NAME(arg1, arg2, ..., argN)
3) 문자열 함수 종류 -1 : 기본
1. 문자열 함수 요약
| 기능 | 분류 | 리턴 값 | 설명 | 예시 |
| ASCII | 값 변환 | number | 주어진 문자열의 첫 번째 문자의 ASCII 코드 값을 반환합니다. | ASCII('Hello') => 72 |
| LENGTH | 값 반환 | number | 문자열의 문자 수를 반환합니다. | LENGTH('Hello') => 5 |
| POSITION | 값 반환 | number | 문자열 내에서 지정된 부분 문자열의 위치를 반환합니다. | POSITION('world' IN 'Hello world') => 7 |
| MD5 | 값 반환 | string | 주어진 문자열의 MD5 해시 값을 계산합니다. | MD5('Hello') => '8b1a9953c4611296a827abf8c47804d7' |
| RIGHT | 값 반환 | string | 문자열의 끝에서 지정된 개수의 문자를 추출합니다. | RIGHT('Hello', 3) => 'llo' |
| LEFT | 값 반환 | string | 문자열의 시작 부분에서 지정된 개수의 문자를 추출합니다. | LEFT('Hello', 3) => 'Hel' |
| REGEXP_MATCHES | 값 반환 | array | 주어진 문자열 내에서 지정된 정규식 패턴과 일치하는 부분 문자열 집합을 반환합니다. | REGEXP_MATCHES('Hello world', 'wo..d') => ['worl'] |
| REGEXP_REPLACE | 값 반환 | string | 주어진 문자열 내에서 지정된 정규식 패턴과 일치하는 부분 문자열을 새 문자열로 대체합니다. | REGEXP_REPLACE('Hello world', 'wo..d', 'goodbye') => 'Hello goodbye' |
| LPAD | 문자열 변환 | string | 왼쪽에서 지정된 길이까지 지정된 문자로 문자열을 채웁니다. | LPAD('42', 5, '0') => '00042' |
| CONCAT | 문자열 변환 | string | 두 개 이상의 문자열을 하나의 문자열로 연결합니다. | CONCAT('Hello', ' world') => 'Hello world' |
| REPLACE | 문자열 조작 | string | 문자열 내에서 지정된 부분 문자열의 모든 발생을 새로운 부분 문자열로 대체합니다. | REPLACE('Hello world', 'world', 'universe') => 'Hello universe' |
| TRIM | 문자열 조작 | string | 문자열의 시작 또는 끝에서 지정된 문자를 제거합니다. | TRIM(' Hello ') => 'Hello' |
| FORMAT | 문자열 변환 | string | 지정된 형식 패턴에 따라 값을 서식화합니다. | FORMAT(1234.5678, '0.00') => '1234.57' |
| CHR | 문자열 변환 | char | ASCII 코드 값을 문자로 변환합니다. | CHR(65) => 'A' |
| SUBSTRING | 문자열 조작 | string | 지정된 위치에서 시작하여 지정된 개수의 문자로 문자열의 부분 문자열을 추출합니다. | SUBSTRING('Hello world', 7, 5) => 'world' |
| TO_CHAR | 문자열 변환 | string | 숫자나 날짜를 지정된 형식을 사용하여 문자열로 변환합니다. | TO_CHAR(1234.5678, '9,999.99') => '1,234.57' |
| TO_NUMBER | 문자열 변환 | number | 문자열을 숫자 값으로 변환합니다. | TO_NUMBER('123.45') => 123.45 |
| SPLIT_PART | 문자열 조작 | string | 지정된 구분 기호를 기준으로 문자열을 여러 부분으로 나누고 지정된 부분을 반환합니다. | SPLIT_PART('apple,banana,orange', ',', 2) => 'banana' |
| TRANSLATE | 문자열 반환 | string | 문자열 내의 일련의 문자를 다른 문자 집합으로 대체합니다. | TRANSLATE('Hello world', 'lo', 'xy') => 'Hexxy wyrd' |
2. ASCII() 함수
💡 ASCII
- 문자(char) 형태를 ‘ASCII 코드 값’을 반환하는 함수입니다.
- 해당 함수는 NUMBER 타입으로 값을 반환합니다.
💡 기본구조 인자
- char : 추출하려는 문자를 의미합니다.
-- 기본구조
ASCII(char)
-- 예시-1
SELECT ASCII('A') -- 65
-- 예시-2
SELECT ASCII('a') -- 97
[ 더 알아보기 ]
💡 매개변수로 ‘문자열’을 입력하면 어떻게 될까?
- 문자열의 ‘첫 번째’ 문자에 대한 ASCII 코드의 값을 반환합니다.
-- 예시-3
SELECT ASCII('ABC') -- 65
3. LENGTH() 함수
💡 LENGTH
- '문자열의 길이'를 반환하는 함수입니다.
- 해당 함수는 NUMBER 타입으로 값을 반환합니다.
💡 기본구조 인자
- string : 추출하려는 문자열을 의미합니다.
-- 기본구조
LENGTH(string);
-- 예시-1
SELECT LENGTH('Hello, world!'); -- 13
-- 예시-2
SELECT LENGTH (CAST(12345 AS TEXT)); -- 5
[ 더 알아보기 ]
💡 공백이나 NULL의 값에 대해서는 어떻게 처리를 하는가?
- 공백의 경우 ‘1’의 값을 가지며 NULL의 값인 경우 ‘NULL’의 값을 반환합니다.
-- 예시-3
SELECT LENGTH(' '); -- 1
-- 예시-4
SELECT LENGTH(NULL); -- NULL
4. POSITION() 함수
💡 POSITION
- 특정 부분 문자열이 다른 문자열 내에서 ‘처음으로 등장하는 위치’를 반환하는 함수입니다.
- 해당 함수는 NUMBER 타입으로 값을 반환합니다.
💡 기본구조 인자
- substring: 찾으려는 문자열을 의미합니다.
- string: 전체 문자열을 의미합니다.
-- 기본구조
POSITION(substring in string)
-- 예시-1
SELECT POSITION('llo' IN 'Hello, world!'); -- 3
[ 더 알아보기 ]
💡 대소문자를 구분을 하여 찾는가?
- 대소문자를 구분하여 동일한 문자열을 찾습니다.
💡 찾으려는 문자(substring)가 전체 문자(string) 내에 존재하지 않을 경우 무엇을 반환하는가?
- 값은 ‘0’을 반환합니다.
SELECT POSITION('tutorial' IN 'PostgreSQL Tutorial'); -- 0
5. MD5() 함수
💡 MD5
- 주어진 문자열의 'MD5 해시 값'을 반환하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string: 해시로 계산될 문자열을 의미합니다.
-- 기본구조
MD5(string)
-- 예시-1
SELECT MD5('PostgreSQL MD5'); -- f78fdb18bf39b23d42313edfaf7e0a44
[ 더 알아보기 ]
💡 MD5는 어떤 암호화 인가?
- 단방향 암호화 함수입니다. 즉, 주어진 문자열을 해시 값으로 변환하는 것은 가능하지만, 해시 값을 다시 원래 문자열로 복원하는 것은 거의 불가능합니다
6. RIGHT() 함수
💡 RIGHT
- 주어진 문자열에서 ‘오른쪽에서 지정된 개수’의 문자를 반환하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string: 가져올 문자열을 의미합니다
- n: 오른쪽 몇번째에서 가져올지를 의미합니다.
-- 기본구조
RIGHT(string, n)
-- 예시-1
SELECT RIGHT('XYZ', 1); -- Z
-- 예시-2
SELECT RIGHT('XYZ', 2); -- YZ
-- 예시-3
SELECT RIGHT('XYZ', - 1); -- YZ
7. LEFT() 함수
💡 LEFT
- 주어진 문자열에서 ‘왼쪽에서 지정된 개수’의 문자를 반환하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string: 가져올 문자열을 의미합니다
- n: 왼쪽 몇번째에서 가져올지를 의미합니다.
-- 기본구조
LEFT(string, n)
-- 예시-1
SELECT LEFT('ABC', 1); -- A
-- 예시-2
SELECT LEFT('ABC', 2); -- AB
-- 예시-3
SELECT LEFT('ABC', -2); -- A
8. REGEXP_MATCHES() 함수
💡 REGEXP_MATCHES
- 주어진 '정규 표현식에 매칭되는 부분 문자열'을 반환하는 함수입니다.
- 해당 함수는 array 타입으로 값을 반환합니다.
💡 기본구조 인자
- source_string: 전체 문자열을 의미합니다.
- pattern: 정규식 패턴을 의미합니다.
- flags: 함수의 동작을 제어합니다.
| flags | 설명 |
| i | 대소문자를 구분하지 않고 매칭합니다. |
| g | 모든 매칭되는 결과를 반환합니다 |
-- 기본구조
REGEXP_MATCHES(source_string, pattern [, flags])
-- 예시-1 : #로 시작하고 영문자나 숫자로 시작하는 문자열을 찾습니다.
SELECT REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES', '#([A-Za-z0-9_]+)', 'g'); -- {PostgreSQL} {REGEX_MATCHES} (2 rows)
-- 예시-2
SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g'); -- {A,BC} (1 row)
9. REGEXP_REPLACE() 함수
💡 REGEXP_REPLACE
- 정규 표현식을 사용하여 문자열에서 '패턴'과 일치하는 부분을 '다른 문자열로 대체'하는 함수입니다.
💡 기본구조 인자
- source_string: 대체를 수행할 원본 문자열입니다.
- pattern: 대체할 패턴을 나타내는 정규 표현식입니다.
- replacement: 패턴과 일치하는 부분을 대체할 문자열입니다.
- flags (옵션): 정규 표현식의 동작을 제어하는 플래그입니다.
| 플래그 | 설명 |
| 'g' | 전역 일치(global match)를 수행합니다. 문자열 내에서 패턴과 일치하는 모든 부분을 대체합니다. |
| 'i' | 대소문자를 구분하지 않고 일치(case-insensitive match)를 수행합니다. |
| 'c' | 대소문자를 구분하여 일치(case-sensitive match)를 수행합니다. |
| 'm' | 여러 줄 일치(multiline match)를 수행합니다. 문자열이 여러 줄로 이루어져 있을 때, 각 줄에서 패턴과 일치하는 부분을 대체합니다. |
| 'n' | 패턴과 일치하는 부분을 대체하지 않고, 일치 여부만 확인합니다. |
-- 기본구조 인자
REGEXP_REPLACE(source_string, pattern, replacement, flags)
-- 예제-1
SELECT REGEXP_REPLACE('Hello123World456', '[0-9]', '', 'g'); --'HelloWorld'
-- 예제-2
SELECT REGEXP_REPLACE('ABC12345xyz','[[:digit:]]','','g'); --'ABCxyz'
10. LPAD() 함수
💡 LPAD
- 지정된 길이에 도달할 때까지 지정된 문자나 문자열로 문자열을 ‘왼쪽에서 채우는 기능’을 제공하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string: 왼쪽에서 채워질 원본 문자열입니다.
- length: 왼쪽 채움이 완료된 후 결과 문자열의 원하는 길이입니다
- fill: 왼쪽 채움에 사용될 문자나 문자열입니다.
-- 기본구조
LPAD(string, length[, fill])
-- 예제-1
SELECT LPAD('hello', 10, 'x'); -- 'xxxxxhello'
-- 예제-2
SELECT LPAD('PostgreSQL',15,'*'); -- '*****PostgreSQL'
11. CONCAT() 함수
💡 CONCAT
- 두 개 이상의 문자열을 결합하여 하나의 문자열로 만드는 기능을 제공하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string1, string2,... : 결합할 하나 이상의 문자열입니다.
-- 기본구조
CONCAT(str_1, str_2, ...)
-- 예제-1
SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'
-- 예제-2
SELECT CONCAT('Hello', ' ', NULL); -- 'Hello '
[ 더 알아보기 ]
💡 CONCAT 함수 말고 ‘||’ 함수도 동일한 기능인가?
- 맞습니다. 아래와 같이 동일하게 두 개 이상의 문자열을 결합하여 하나의 문자열로 만듭니다.
-- 예제-1
SELECT 'Concatenation' || ' ' || 'Operator' AS result_string; -- Concatenation Operator
-- 예제-2
SELECT 'Concat with ' || NULL AS result_string; -- NULL
12. REPLACE() 함수
💡 REPLACE
- 문자열에서 지정된 부분 문자열을 다른 문자열로 ‘대체’하는 기능을 제공하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- source: 대체를 수행할 원본 문자열입니다.
- old_text: 대체할 대상 문자열입니다.
- new_text: 대체될 문자열입니다.
-- 기본구조
REPLACE(source, old_text, new_text );
-- 예제-1
SELECT REPLACE('Hello, world!', 'world', 'everyone'); -- 'Hello, everyone!'
-- 예제-2
SELECT REPLACE ('ABC AA', 'A', 'Z'); -- 'ZBC ZZ'
13. TRIM() 함수
💡 TRIM
- 문자열의 양 끝에 있는 공백을 제거하는 기능을 제공합니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- leading: 문자열의 시작 부분에서 공백을 제거합니다.
- trailing: 문자열의 끝 부분에서 공백을 제거합니다.
- both: 문자열의 시작과 끝 부분에서 공백을 제거합니다. (기본값)
- characters: 제거할 문자나 문자열을 지정합니다. 생략하면 공백 문자가 제거됩니다.
-- 기본 구조
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
-- 예제-1
SELECT TRIM(' hello '); -- 'hello'
[ 더 알아보기 ]
💡 LTRIM, RTRIM, BTRIM 함수는 무엇일까?
- LTRIM 함수는 문자열의 왼쪽(시작 부분)에 있는 공백을 제거합니다.
- RTRIM 함수는 문자열의 오른쪽(끝 부분)에 있는 공백을 제거합니다.
- BTRIM 함수는 문자열의 양쪽(시작과 끝 부분)에 있는 공백을 제거합니다.
- 각각의 함수는 문자열 데이터의 전처리 또는 비교를 수행할 때 유용하게 사용될 수 있습니다.
14. FORMAT() 함수
💡 FORMAT
- 숫자 또는 날짜/시간 값을 지정된 형식으로 포맷팅 하는 기능을 제공합니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본 구조
- format_string: 포맷팅을 지정하기 위한 형식 문자열입니다.
- value1, value2, ...: 포맷팅을 적용할 값들입니다. 포맷 문자열에서 %s 또는 %L과 같은 일치하는 위치에 값이 삽입됩니다.
-- 기본구조
FORMAT(format_string, value1, value2, ...)
SELECT FORMAT('The price is $%.2f', 10.5); -- 'The price is $10.50'
SELECT FORMAT('Hello, %s','PostgreSQL'); -- 'Hello, PostgreSQL'
15. CHR() 함수
💡 CHR
- 주어진 숫자에 해당하는 ASCII 문자를 반환하는 기능을 제공합니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- integer: ASCII 문자 코드를 나타내는 정수 값입니다.
-- 기본구조 인자
CHR(integer)
SELECT CHR(65); -- 'A'
SELECT CHR(97); -- 'a'
16. SUBSTRING() 함수
💡 SUBSTRING
- 문자열에서 지정된 부분 문자열을 추출하는 기능을 제공합니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- string: 부분 문자열을 추출할 원본 문자열입니다.
- start_position: 추출을 시작할 위치입니다. 첫 번째 문자는 1로 시작합니다.
- length (옵션): 추출할 부분 문자열의 길이입니다. 생략하면 시작 위치부터 문자열의 끝까지 추출됩니다.
-- 기본구조-1
SUBSTRING (string ,start_position , length )
-- 기본구조-2
SUBSTRING(string FROM start_position [FOR length])
SELECT SUBSTRING ('PostgreSQL', 1, 8); -- PostgreS
SELECT SUBSTRING ('PostgreSQL', 8); -- SQL
-- 예시-1
SELECT SUBSTRING('Hello, world!' FROM 1 FOR 5); -- 'Hello'
17. TO_CHAR() 함수
💡 TO_CHAR
- 숫자나 날짜/시간 값을 지정된 형식으로 문자열로 변환하는 기능을 제공합니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- value: 문자열로 변환할 숫자나 날짜/시간 값입니다.
- format: 변환된 문자열의 형식을 지정하는 형식 문자열입니다.
-- 기본구조 인자
TO_CHAR(value, format)
-- 예시-1
SELECT TO_CHAR(1234.56, '9999.99'); -- '1234.56'
💡 format 형식
| 형식 | 설명 |
| 9 | 지정된 자릿수의 숫자 값 |
| 0 | 앞에 0이 붙은 숫자 값 |
| . (점) | 소수점 |
| D | 로캘을 사용하는 소수점 |
| , (콤마) | 그룹 (천 단위) 구분 기호 |
| FM | 패딩 공백과 앞부분의 0을 제거하는 채우기 모드 |
| PR | 각도 괄호 안의 음수 값 |
| S | 로캘을 사용하는 숫자에 고정된 부호 |
| L | 로캘을 사용하는 통화 기호 |
| G | 로캘을 사용하는 그룹 구분 기호 |
| MI | 0보다 작은 숫자의 지정된 위치에 있는 음수 부호 |
| PL | 0보다 큰 숫자의 지정된 위치에 있는 양수 부호 |
| SG | 지정된 위치에 있는 양수/음수 부호 |
| RN | 1부터 3999까지 범위의 로마 숫자 |
| TH 또는 th | 대문자 또는 소문자 서수 접미사 |
18. TO_NUMBER() 함수
💡 TO_NUMBER
- 문자열을 숫자로 변환하는 기능을 제공합니다.
- 해당 함수는 NUMBER 타입으로 값을 반환합니다.
💡 기본구조 인자
- source_string: 숫자로 변환할 문자열입니다.
- format: 변환된 숫자의 형식을 지정하는 형식 문자열입니다.
-- 기본구조
TO_NUMBER(source_string, format)
-- 예시
SELECT TO_NUMBER('1234.56', '9999.99'); -- 1234.56| format 종류 | 설명 |
| 9 | 지정된 자릿수의 숫자 값 |
| 0 | 선행 0이 있는 숫자 값 |
| . (점) | 소수점 |
| D | 로캘을 사용하는 소수점 |
| , (쉼표) | 그룹 (천 단위) 구분 기호 |
| FM | 패딩 공백과 선행 0을 억제하는 채우기 모드 |
| PR | 꺽쇠 괄호로 표시된 음수 값 |
| S | 로캘을 사용하는 숫자에 고정된 부호 |
| L | 로캘을 사용하는 통화 기호 |
| G | 로캘을 사용하는 그룹 구분 기호 |
| MI | 0보다 작은 숫자에 대해 지정된 위치에 마이너스 부호 |
| PL | 0보다 큰 숫자에 대해 지정된 위치에 플러스 부호 |
| SG | 지정된 위치에 플러스 / 마이너스 부호 |
| RN | 1에서 3999까지 범위에 있는 로마 숫자 |
| TH 또는 th | 대문자 또는 소문자 서수 접미사 |
19. SPLIT_PART() 함수
💡 SPLIT_PART
- 지정된 구분자로 분리된 문자열에서 특정 위치에 있는 부분 문자열을 추출하는 기능을 제공하는 함수입니다.
- 해당 함수는 STRING 타입으로 값을 반환합니다.
💡 기본구조 인자
- source_string: 부분 문자열을 추출할 원본 문자열입니다.
- delimiter: 문자열을 분리하는 데 사용할 구분자입니다.
- position: 추출할 부분 문자열의 위치입니다. 첫 번째 부분 문자열은 1로 시작합니다.
-- 기본구조
SPLIT_PART(source_string, delimiter, position)
-- 예제-1
SELECT SPLIT_PART('apple,banana,orange', ',', 2); -- 'banana'
-- 예제-2
SELECT SPLIT_PART('A,B,C', ',', 2); -- 'B'
20. TRANSLATE() 함수
💡 TRANSLATE
- 문자열에서 지정된 문자나 문자열을 다른 문자나 문자열로 치환하는 기능을 제공하는 함수입니다.
💡 기본구조 인자
- source_string: 치환을 수행할 원본 문자열입니다.
- from_string: 치환할 대상 문자나 문자열을 지정합니다.
- to_string: 대상 문자나 문자열을 치환할 문자나 문자열로 지정합니다.
-- 기본구조
TRANSLATE(source_string, from_string, to_string)
-- 예제-1
SELECT TRANSLATE('hello world', 'ol', 'ab');
-- 예제-2
SELECT TRANSLATE('12345', '134', 'ax') -- a2x5
4) 문자열 함수 종류 -2: 심화
| 기능 | 분류 | 리턴값 | 설명 | 예시 |
| CONCAT_WS | 값 변환 | string | 여러 문자열을 구분 기호로 연결합니다. | SELECT CONCAT_WS(', ', 'apple', 'banana', 'orange'); -- 'apple, banana, orange' |
| INITCAP | 값 변환 | string | 문자열의 단어를 대문자로 변환합니다. | SELECT INITCAP('hello, world'); -- 'Hello, World' |
| LOWER | 값 변환 | string | 문자열을 소문자로 변환합니다. | SELECT LOWER('HELLO'); -- 'hello' |
| UPPER | 값 변환 | string | 문자열을 대문자로 변환합니다. | SELECT UPPER('hello'); -- 'HELLO' |
| REPEAT | 값 변환 | string | 문자열을 지정된 횟수만큼 반복합니다. | SELECT REPEAT('abc', 3); -- 'abcabcabc' |
| LTRIM | 값 변환 | string | 문자열의 왼쪽에서 지정된 문자를 제거합니다. | SELECT LTRIM(' hello ', ' '); -- 'hello ' |
| RTRIM | 값 변환 | string | 문자열의 오른쪽에서 지정된 문자를 제거합니다. | SELECT RTRIM(' hello ', ' '); -- ' hello' |
오늘도 감사합니다. 😀
반응형
'DB > API Document' 카테고리의 다른 글
| [DB/Postgres] PostgreSQL 데이터 타입의 종류 이해하기 (1) | 2023.12.21 |
|---|---|
| [DB/postgres] 윈도우 함수(Window Function) 이해 및 활용방법 (0) | 2023.09.28 |
| [DB/Postgres] 날짜 함수(Date Function) 이해 및 활용방법 (4) | 2023.09.16 |
| [DB/Postgres] 수학 함수(Math Function) 이해 및 활용방법 (0) | 2023.09.13 |
| [DB/Postgres] 집계 함수(Aggregate Function) 이해 및 활용방법 (0) | 2023.09.10 |