重新格式化字符串和字符数据

在 PostgreSQL 中使用函数处理数据

Brian Piccolo

Sr. Director, Digital Strategy

主题

  • 重新格式化字符串和字符数据。
  • 解析字符串和字符数据。
  • 确定字符串长度和字符位置。
  • 截断与填充字符串数据。
在 PostgreSQL 中使用函数处理数据

字符串连接运算符

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    |
+------------+-----------+-------------------+
在 PostgreSQL 中使用函数处理数据

用函数连接字符串

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    |
+--------------------------------------------+
在 PostgreSQL 中使用函数处理数据

与非字符串输入连接

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

+-------------------+
| full_name         |
|-------------------|
| 1: MARY SMITH     |
| 2: LINDA WILLIAMS |
+-------------------+
在 PostgreSQL 中使用函数处理数据

更改字符串大小写

SELECT 
  UPPER(email) 
FROM customer;

+-------------------------------------+
| UPPER(email)                        |
|-------------------------------------|
| [email protected]       |
| [email protected] |
| [email protected]   |
+-------------------------------------+
在 PostgreSQL 中使用函数处理数据

更改字符串大小写

SELECT 
  LOWER(title) 
FROM film;

+-------------------+
| LOWER(title)      |
|-------------------|
| academy dinosaur  | 
| ace goldfinger    |
| adaptation holes  |
+-------------------+
在 PostgreSQL 中使用函数处理数据

更改字符串大小写

SELECT 
  INITCAP(title) 
FROM film;

+-------------------+
| INITCAP(title)    |
|-------------------|
| Academy Dinosaur  | 
| Ace Goldfinger    |
| Adaptation Holes  |
+-------------------+
在 PostgreSQL 中使用函数处理数据

替换字符串中的字符

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...      |
+---------------------------------------------------------+
在 PostgreSQL 中使用函数处理数据

替换字符串中的字符

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...   |
+---------------------------------------------------------+
在 PostgreSQL 中使用函数处理数据

用 REVERSE 处理字符串数据

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

+-------------------------------------+
| title            | reverse(title)   |
|-------------------------------------|
| ACADEMY DINOSAUR | RUASONID YMEDACA |
| ACE GOLDFINGER   | REGNIFDLOG ECA   |
+-------------------------------------+
在 PostgreSQL 中使用函数处理数据

开始练习吧！

在 PostgreSQL 中使用函数处理数据

Preparing Video For Download...