文字列と文字データの再設定

PostgreSQL のデータ操作関数

Brian Piccolo

Sr. Director, Digital Strategy

トピック

*文字列と文字データの再設定。

文字列と文字データの解析。
文字列の長さと文字位置の判定。
文字列データの切り詰めとパディング。

文字列連結演算子

SELECT 
  first_name,
  last_name,
  first_name || ' ' || last_name AS full_name
FROM customer

+------------+-----------+-------------------+
| first_name | last_name | full_name         |
|------------|-----------|-------------------|
| MARY       | SMITH     | MARY SMITH        |
| LINDA      | WILLIAMS  | LINDA WILLIAMS    |
+------------+-----------+-------------------+

関数を使った文字列連結

SELECT 
  CONCAT(first_name,' ', last_name) AS full_name
FROM customer;

+--------------------------------------------+
| first_name | last_name | full_name         |
|--------------------------------------------|
| MARY       | SMITH     | MARY SMITH        |
| LINDA      | WILLIAMS  | LINDA WILLIAMS    |
+--------------------------------------------+

文字列以外の入力による文字列連結

SELECT 
  customer_id || ': ' 
  || first_name || ' ' 
  || last_name AS full_name
FROM customer;

+-------------------+
| full_name         |
|-------------------|
| 1: MARY SMITH     |
| 2: LINDA WILLIAMS |
+-------------------+

文字列の大文字・小文字の変更

SELECT 
  UPPER(email) 
FROM customer;

+-------------------------------------+
| UPPER(email)                        |
|-------------------------------------|
| [email protected]       |
| [email protected] |
| [email protected]   |
+-------------------------------------+

文字列の大文字・小文字の変更

SELECT 
  LOWER(title) 
FROM film;

+-------------------+
| LOWER(title)      |
|-------------------|
| academy dinosaur  | 
| ace goldfinger    |
| adaptation holes  |
+-------------------+

文字列の大文字・小文字の変更

SELECT 
  INITCAP(title) 
FROM film;

+-------------------+
| INITCAP(title)    |
|-------------------|
| Academy Dinosaur  | 
| Ace Goldfinger    |
| Adaptation Holes  |
+-------------------+

文字列の文字の置換

SELECT description FROM film;

+---------------------------------------------------------+
| description                                             |
|---------------------------------------------------------|
| A Epic Drama of a Feminist And a Mad Scientist...       |
| A Astounding Epistle of a Database Administrator...     |
| A Astounding Reflection of a Lumberjack And a Car...    |
| A Fanciful Documentary of a Frisbee And a Lumberjack... |
| A Fast-Paced Documentary of a Pastry Chef And a...      |
+---------------------------------------------------------+

文字列の文字の置換

SELECT 
    REPLACE(description, 'A Astounding', 
              'An Astounding') as description 
FROM film;

+---------------------------------------------------------+
| description                                             |
|---------------------------------------------------------|
| A Epic Drama of a Feminist And a Mad Scientist...       |
| An Astounding Epistle of a Database Administrator...    |
| An Astounding Reflection of a Lumberjack And a Car...   |
+---------------------------------------------------------+

REVERSEを使った文字列データの操作

SELECT 
  title, 
  REVERSE(title) 
FROM 
  film AS f;

+-------------------------------------+
| title            | reverse(title)   |
|-------------------------------------|
| ACADEMY DINOSAUR | RUASONID YMEDACA |
| ACE GOLDFINGER   | REGNIFDLOG ECA   |
+-------------------------------------+

練習しましょう！

PostgreSQL のデータ操作関数