Parsing string and character data
Functions for Manipulating Data in PostgreSQL
Brian Piccolo
Sr. Director, Digital Strategy
Determining the length of a string
SELECT
title,
CHAR_LENGTH(title)
FROM film;
+-------------------+---------------------+
| title | CHAR_LENGTH(title) |
|-------------------+---------------------|
| ACADEMY DINOSAUR | 16 |
| ACE GOLDFINGER | 14 |
| ADAPTATION HOLES | 16 |
+-------------------+---------------------+
Determining the length of a string
SELECT
title,
LENGTH(title)
FROM film;
+-------------------+----------------+
| title | LENGTH(title) |
|-------------------+----------------|
| ACADEMY DINOSAUR | 16 |
| ACE GOLDFINGER | 14 |
| ADAPTATION HOLES | 16 |
+-------------------+----------------+
Finding the position of a character in a string
SELECT
email,
POSITION('@' IN email)
FROM customer;
+-------------------------------------+------------------------+
| email | POSITION('@' IN email) |
|-------------------------------------|------------------------|
| [email protected] | 11 |
| [email protected] | 17 |
| [email protected] | 15 |
+-------------------------------------+------------------------+
Finding the position of a character in a string
SELECT
email,
STRPOS(email, '@')
FROM customer;
+-------------------------------------+--------------------+
| email | STRPOS(email, '@') |
|-------------------------------------|--------------------|
| [email protected] | 11 |
| [email protected] | 17 |
| [email protected] | 15 |
+-------------------------------------+--------------------+
Parsing string data
SELECT
LEFT(description, 50)
FROM film;
+----------------------------------------------------+
| description |
|----------------------------------------------------|
| A Epic Drama of a Feminist And a Mad Scientist who |
| A Astounding Epistle of a Database Administrator A |
| A Astounding Reflection of a Lumberjack And a Car |
+----------------------------------------------------+
Parsing string data
SELECT
RIGHT(description, 50)
FROM film;
+----------------------------------------------------+
| description |
|----------------------------------------------------|
| who must Battle a Teacher in The Canadian Rockies |
| nd a Explorer who must Find a Car in Ancient China |
| Car who must Sink a Lumberjack in A Baloon Factory |
+----------------------------------------------------+
Extracting substrings of character data
SELECT
SUBSTRING(description, 10, 50)
FROM
film AS f;
+----------------------------------------------------+
| description |
|----------------------------------------------------|
| ama of a Feminist And a Mad Scientist who must Bat |
| ing Epistle of a Database Administrator And a Expl |
| ing Reflection of a Lumberjack And a Car who must |
+----------------------------------------------------+
Extracting substrings of character data
SELECT
SUBSTRING(email FROM 0 FOR POSITION('@' IN email))
FROM
customer;
+----------------------------------------------------+
| SUBSTRING(email FROM 0 FOR POSITION('@' IN email)) |
|----------------------------------------------------|
| MARY.SMITH |
| PATRICIA.JOHNSON |
| LINDA.WILLIAMS |
+----------------------------------------------------+
Extracting substrings of character data
SELECT
SUBSTRING(email FROM POSITION('@' IN email)+1 FOR CHAR_LENGTH(email))
FROM
customer;
+-----------------------------------------------------------------------+
| SUBSTRING(email FROM POSITION('@' IN email)+1 FOR CHAR_LENGTH(email)) |
|-----------------------------------------------------------------------|
| sakilacustomer.org |
| sakilacustomer.org |
| sakilacustomer.org |
+-----------------------------------------------------------------------+
Extracting substrings of character data
SELECT
SUBSTR(description, 10, 50)
FROM
film AS f;
+----------------------------------------------------+
| description |
|----------------------------------------------------|
| ama of a Feminist And a Mad Scientist who must Bat |
| ing Epistle of a Database Administrator And a Expl |
| ing Reflection of a Lumberjack And a Car who must |
+----------------------------------------------------+
Let's practice!
Functions for Manipulating Data in PostgreSQL
