이번 포스팅에서는 PostgreSQL, Greenplum Database의 문자열 데이터형 (Character Types)의 종류와, 생성, 운영자, 함수 등에 대해서 알아보겠습니다. 


먼저, PostgreSQL 의 문자열형 종류는 아래처럼 

 - (1) 고정 길이 n 바이트 이내의 문자열 character(n), char(n)

 - (2) 가변 길이 n 바이트 이내의 문자열 varchar(n)

 - (3) 제한 없는 가변 길이 문자열 text, varchar

의 3가지 종류가 있습니다. 

PostgreSQL은 이들 3가지 문자열 종류별로 성능 차이는 없습니다.




  (1) PostgreSQL 문자열형 포함한 테이블 생성 및 문자열 입력


위에서 소개한 3개의 문자열형 종류인 CHAR(2), VARCHAR(10), TEXT 를 포함하고 있는 예제 테이블을 만들어보겠습니다. 괄호안의 숫자는 Bytes 길이를 의미합니다. 


-- Create a table for an example

DROP TABLE IF EXISTS char_type_test;

CREATE TABLE char_type_test (

id SERIAL PRIMARY KEY 

, col_1 CHAR(2)

, col_2 VARCHAR(10)

, col_3 TEXT

);




아래의 두가지 경우는 CHAR(2), VARCHAR(10) 처럼 바이트 길이 제약이 있는데 이를 만족하지 않는 경우 SQL ERROR가 발생한 예입니다. 



(1-1) col_1 의 바이트 길이가 CHAR(2) 와 정확하게 맞지 않아서 에러가 나는 경우



-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character(2)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hollo'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);





(1-2) col_2 의 바이트 길이가 VARCHAR(10) 보다 길어서 에러가 나는 경우


-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character varying(10)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);





(1-3) col_1, col_2, col_3 가 모두 문자열형 조건을 만족하여 정상 입력된 경우



-- No error

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL'

, 'I love PostgreSQL all the time')

, (

'Hi'

, 'Greenplum'

, 'I love Greenplum too'

);


SELECT * FROM char_type_test ORDER BY id;






  (2) PostgreSQL 문자열 연산자 (Operator)와 함수 (Functions)



(2-1) 문자열 합치기 (String Concatenation)


  • 문자열 합치기: '||' 연산자
  • 문자열 합치기: CONCAT() 함수
  • 구분자를 포함하여 문자열 합치기: CONCAT_WS() 함수


(2-1-1) 문자열 합치기 연산자: '||'


지난번 포스팅에서는 PostgreSQL의 4가지 유형의 연산자로서 산술 연산자, 비교 연산자, 논리 연산자, 비트 연산자에 대해서 소개하였는데요, 문자열에 특화된 연산자 중에서 두 문자열을 합칠 때 사용하는 '||' 연산자를 소개하겠습니다. 


string || string

string || non-string or non-string || string



SELECT

*

, col_1 || ', ' || col_2 AS db_name

, id || ' : ' || col_2 AS db_id

FROM char_type_test;





(2-1-2) 두 문자열 합치기 함수: CONCAT()


위의 (2-1-1)과 동일한 결과를 concat() 함수를 사용하여 얻을 수 있습니다. 



SELECT 

*

, CONCAT(col_1, ', ', col_2) AS db_name_2

, CONCAT(id, ' : ', col_2) AS db_id_2

FROM char_type_test;





문자열을 합치는데 사용하는 '||' 연산자와 CONCAT() 함수 사이에는 NULL 값을 처리하는데 있어 차이가 존재합니다. '||' 연산자는 NULL 값이 있으면 NULL 값을 반환하는 반면에, CONCAT() 함수는 NULL 값을 무시하고 나머지 값을 반환합니다. 



-- Concatenate using || with a NULL value returns NULL

SELECT col_1 || NULL AS result_string FROM char_type_test;




-- Concat function ignores NULL arguments.

SELECT CONCAT(col_1, NULL) AS result_string_2 FROM char_type_test;





(2-1-3) 구분자를 포함하여 문자열을 합치기 (CONCAT With Separator): CONCAT_WS()



-- (3) CONCAT_WS : CONCAT With Separator

-- : CONCAT_WS(separator, str_1, str_2, ...);

SELECT 

*

, CONCAT_WS(', ', col_1, col_2) AS db_name_3

, CONCAT_WS(' : ', id, col_2) AS db_id_3

FROM char_type_test;





합치려는 문자열의 개수가 여러개이고 구분자를 포함시키고 싶을 경우, CATCAT_WS() 함수는 구분자를 한번만 써줘도 되므로, 구분자를 매번 써줘어야 하는 CATCAT() 함수보다 편리합니다. 



-- Useful when there are lots of values to concatenate

SELECT CONCAT('a', ': ', 'b', ': ', 'c', ': ', 'd', ': ', 'e', ': ', 'f') AS concat_result_1;





SELECT CONCAT_WS(': ', 'a', 'b', 'c', 'd', 'e', 'f') AS concat_ws_result_2;






(2-2) 문자열 길이 (String Length) 


  • 문자열 내 비트의 길이: BIT_LENGTH(string)
  • 문자열 내 바이트의 길이: OCTET_LENGTH(string)
  • 문자열 내 문자 길이: LENGTH(string)



SELECT

col_3

, BIT_LENGTH(col_3) AS bit_len_col_3

, OCTET_LENGTH(col_3) AS cotet_len_col_3

, LENGTH(col_3) AS char_len_col_3

FROM char_type_test;




문자열 내 바이트의 길이를 재는 OCTET_LENGTH()와 문자 길이를 재는 LENGTH() 함수가 알파벳과 숫자에서는 차이가 없는데요, 한글처럼 문자열 내 바이트와 문자 길이가 다른 경우도 있습니다. 따라서 한글의 바이트를 재고 싶다면 LENGTH()가 아니라 OCTET_LENGTH() 함수를 사용해야 겠습니다. 


 SELECT OCTET_LENGTH('abc123');

 ---------

 result

 6

 SELECT LENGTH('abc123');

 ----------

 result 

 6


 SELECT OCTET_LENGTH('안녕하세요');

 ----------

 result

 15

 SELECT LENGTH('안녕하세요');

 ----------

 result 

 5




(2-3) 문자열 대/소문자 변환 (String Case Conversion)


  • 문자열 내 문자를 소문자로 바꾸기: LOWER(string)
  • 문자열 내 문자를 대문자로 바꾸기: UPPER(string)
  • 문자열 내 첫번째 문자를 대문자로, 나머지 문자는 소문자로 바꾸기: INITCAP(string)


-- lower(string): convert a string to lower case

-- upper(string): convert a string to upper case

-- initcap(string): convert words in a string to title case

SELECT 

col_2

, LOWER(col_2) AS lower_col_2 

, UPPER(col_2) AS upper_col_2 

, INITCAP(col_2) AS initcap_col_2

FROM char_type_test;






(2-4) 문자열 겹쳐쓰기, 바꾸기, 뒤집기, 반복하기


  • 문자열 겹쳐쓰기: OVERLAY(string PLACING string FROM int [FOR int])
  • 문자열 바꾸기: REPLACE(string, FROM, TO)

아래 예에서 OVERLAY() 함수에 대해 부언설명하자면요, 'col_3' 칼럼에서 8번째 자리부터 시작해서 10개 문자에 해당하는 부분을 'Opensource DB' 라는 새로운 문자열로 덮어쓰기(overlay)를 하라는 뜻입니다. 


-- overlay(string placing string from int [for int])

-- replace(string, from, to)

SELECT 

col_3

, OVERLAY(col_3 PLACING 'Opensource DB ' FROM 8 FOR 10) AS overlayed_col_3

, REPLACE(col_3, 'I', 'You') AS replaced_col_3

FROM char_type_test;


 



  • 문자열 순서 뒤집기: REVERSE(string)
  • 문자열 n번 반복하기: REPEAT(string, int)


-- reverse(string)

-- repeat(string, int)

SELECT 

col_2

, REVERSE(col_2) AS reversed_col_2

, REPEAT(col_2, 3) AS repeated_col_2

FROM char_type_test;


 




(2-5) 문자열의 위치 인덱스: POSITION(substring IN string)


POSITION() 함수이 첫번째 인자로 받는 substring 은 대/소문자를 구분합니다. 



----------------------------------

-- Location of specified substring

----------------------------------

SELECT 

col_3

, POSITION('g' in col_3) AS g_position

, POSITION('G' in col_3) AS "G_position"

, POSITION('love' in col_3) AS love_position

FROM char_type_test;






(2-6) 문자열의 부분 문자열을 잘라오기: SUBSTR(string [from int] [for int])



-- substring(string [from int] [for int])

SELECT 

*

, SUBSTR(col_2, 1, 3) AS substr_col_2_3

, SUBSTR(col_2, 1, 5) AS substr_col_2_5

FROM char_type_test;


 




(2-7) 문자열을 구분자(Delimeter)를 기준으로 분할 후 일부분 가져오기

: SPLIT_PART(string, delimiter, part_position)



--------------

-- Split a string on a specified delimeter

--------------

SELECT 

'2020-07-04' AS yyyymmdd

, SPLIT_PART('2020-07-04', '-', 1) AS year

, SPLIT_PART('2020-07-04', '-', 2) AS month

, SPLIT_PART('2020-07-04', '-', 3) AS day;


 




(2-8) 문자열 내 특정 하위 문자열을 잘라내기: TRIM()


  • 문자열 내 특정 문자로 시작하는 부분 잘라내기: TRIM(leadnig characters from string)
  • 문자열 내 특정 문자로 끝나는 부분 잘라내기: TRIM(trailing characters from string)
  • 문자열 내 특정 문자로 시작하거나 끝나는 부분 잘라내기: TRIM(both characters from string)
  • 문자열의 왼쪽 시작부분에 특정 문자가 있으면 잘라내기: LTRIM(string, characters)
  • 문자열의 오른쪽 끝부분에 특정 문자가 있으면 잘라내기: RTRIM(string, characters)

TRIM()과 LTRIM(), RTRIM() 간에 인자의 위치가 조금 다른 것에 주의하세요. 



--  trim([leading | trailing | both] [character] from string)

SELECT 

'xTomxx' AS original_str

, TRIM(LEADING 'x' FROM 'xTomxx') AS trim_leading

, TRIM(TRAILING 'x' FROM 'xTomxx') AS trim_trailing

, TRIM(BOTH 'x' FROM 'xTomxx') AS trim_both

, LTRIM('xTomxx', 'x') AS l_trim

, RTRIM('xTomxx', 'x') AS r_trim;






(2-9) 문자열의 처음 n개 문자 가져오기


  • 문자열의 왼쪽으로(시작) 부터 처음 n개 문자 가져오기: LEFT(string, int n)
  • 문자열의 오른쪽으로(끝) 부터 처음 n개 문자 가져오기: RIGHT(string, int n)


--------------------------------

-- First n character in a string

--------------------------------

SELECT 

col_2

, LEFT(col_2, 3) AS first_n_from_left

, RIGHT(col_2, 3) AS first_n_from_right

FROM char_type_test;


 




(2-10) 문자열의 길이가 n개보다 모자라는 개수만큼 채우기(Padding)


  • 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 왼쪽부터 채우기
    : LPAD(string, int n, character)
  • 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 오른쪽부터 채우기
    : RPAD(string, int n, character)

아래 예에서는 id가 정수형(int) 이므로 id::char 을 사용하여 문자형으로 변환한 후에, 문자열형 함수인 LPAD(), RPAD() 함수를 적용하였습니다. 


-----------------------------------

-- PAD on the left or rght a string 

-----------------------------------

SELECT 

id

, LPAD(id::char, 5, '0') AS lpad_id

, RPAD(id::char, 5, '0') AS rpad_id

FROM char_type_test;


 



문자열형의 패턴 매칭(pattern matching)까지 다루기에는 포스팅이 너무 길어지므로 다음번에 별도로 소개하겠습니다. 


많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)


반응형
Posted by Rfriend
TAG , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

댓글을 달아 주세요