[DB/Postgres] 문자열 함수(String Function) 이해 및 활용방법

해당 글에서는 데이터베이스 PostgreSQL을 기반으로 문자열 함수(String Function)에 대해 알아봅니다.

 
 

1) 문자열 함수(String Function)

---

💡 문자열 함수(String Function)

-  문자열을 조작하고 변환하는 데 사용되는 함수들을 의미합니다.

PostgreSQL String Functions

 
 

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'

 
 
 
 
오늘도 감사합니다. 😀