Contents

String Manipulation Functions

We use string manipulation functions quite extensively. Here are some of the important functions which we typically use.

  • Case Conversion - lower, upper, initcap

  • Getting size of the column value - length

  • Extracting Data - substr and split_part

  • Trimming and Padding functions - trim, rtrim, ltrim, rpad and lpad

  • Reversing strings - reverse

  • Concatenating multiple strings concat and concat_ws

Case Conversion and Length

Let us understand how to perform case conversion of a string and also get length of a string.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

  • Case Conversion Functions - lower, upper, initcap

%%sql

SELECT lower('hEllo wOrlD') AS lower_result,
    upper('hEllo wOrlD') AS upper_result,
    initcap('hEllo wOrlD') AS initcap_result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
lower_result upper_result initcap_result
hello world HELLO WORLD Hello World

  • Getting length - length

%%sql

SELECT length('hEllo wOrlD') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
11

Let us see how to use these functions on top of the table. We will use orders table which was loaded as part of last section.

  • order_status for all the orders is in upper case and we will convert every thing to lower case.

%%sql

SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status
1021 2013-07-30 00:00:00 10118 COMPLETE
4068 2013-08-17 00:00:00 12293 PENDING
5881 2013-08-30 00:00:00 3715 CLOSED
7564 2013-09-09 00:00:00 8648 CLOSED
8766 2013-09-18 00:00:00 855 COMPLETE
8926 2013-09-19 00:00:00 10517 ON_HOLD
9290 2013-09-21 00:00:00 11879 COMPLETE
9793 2013-09-24 00:00:00 9809 COMPLETE
9816 2013-09-24 00:00:00 1753 COMPLETE
14047 2013-10-20 00:00:00 6473 CLOSED 
%%sql

SELECT order_id, order_date, order_customer_id,
    lower(order_status) AS order_status,
    length(order_status) AS order_status_length
FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status order_status_length
1021 2013-07-30 00:00:00 10118 complete 8
4068 2013-08-17 00:00:00 12293 pending 7
5881 2013-08-30 00:00:00 3715 closed 6
7564 2013-09-09 00:00:00 8648 closed 6
8766 2013-09-18 00:00:00 855 complete 8
8926 2013-09-19 00:00:00 10517 on_hold 7
9290 2013-09-21 00:00:00 11879 complete 8
9793 2013-09-24 00:00:00 9809 complete 8
9816 2013-09-24 00:00:00 1753 complete 8
14047 2013-10-20 00:00:00 6473 closed 6

Extracting Data - substr and split_part

Let us understand how to extract data from strings using substr/substring as well as split_part.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

  • We can extract sub string from main string using substr or substring position and length.

  • For example, get first 4 characters from date to get year or get last 4 characters from fixed length unique id.

  • substring have broader options (regular expression) and also can be used with different styles (using keywords such as FROM, FOR).

  • Unlike in other relational databases, we cannot pass negative integers to substr or substring to get the information from right. We need to use functions like right instead.

%%sql

SELECT substr('2013-07-25 00:00:00.0', 1, 4) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
2013 
%%sql

SELECT substring('2013-07-25 00:00:00.0', 1, 4) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
2013 
%%sql

SELECT substring('2013-07-25 00:00:00.0' FROM 1 FOR 4) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
2013 
%%sql

SELECT substring('2013-07-25 00:00:00.0', 6, 2) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
07 
%%sql

SELECT substring('2013-07-25 00:00:00.0', 9, 2) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
25 
%%sql

SELECT substring('2013-07-25 00:00:00.0' from 12) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
00:00:00.0 
%%sql

SELECT substr('2013-07-25 00:00:00.0', 12) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
00:00:00.0 
%%sql

SELECT right('123 456 7890', 4) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
7890 
%%sql

SELECT left('123 456 7890', 3) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
123

Note

We can also use combination of substring and length like below to get last 4 digits or characters from a string.

%%sql

SELECT substring('123 456 7890' FROM length('123 456 7890') - 4) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
7890 
%%sql

SELECT substring('123 456 7890' FROM '....$') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
7890

Note

Getting first 3 characters or digits as well as last 4 characters or digits using substring. However, this works only when the strings are of fixed length.

%%sql

WITH unique_ids AS (
    SELECT '241-80-7115' AS unique_id UNION
    SELECT '694-30-6851' UNION
    SELECT '586-92-5361' UNION
    SELECT '884-65-284' UNION
    SELECT '876-99-585' UNION
    SELECT '831-59-5593' UNION
    SELECT '399-88-3617' UNION
    SELECT '733-17-4217' UNION
    SELECT '873-68-9778' UNION
    SELECT '48'
) SELECT unique_id,
    substring(unique_id FROM 1 FOR 3) AS unique_id_first3,
    substring(unique_id FROM '....$') AS unique_id_last4
FROM unique_ids
ORDER BY unique_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
unique_id unique_id_first3 unique_id_last4
241-80-7115 241 7115
399-88-3617 399 3617
48 48 None
586-92-5361 586 5361
694-30-6851 694 6851
733-17-4217 733 4217
831-59-5593 831 5593
873-68-9778 873 9778
876-99-585 876 -585
884-65-284 884 -284

  • Let us see how we can extract date part from order_date of orders.

%%sql

SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status
1021 2013-07-30 00:00:00 10118 COMPLETE
4068 2013-08-17 00:00:00 12293 PENDING
5881 2013-08-30 00:00:00 3715 CLOSED
7564 2013-09-09 00:00:00 8648 CLOSED
8766 2013-09-18 00:00:00 855 COMPLETE
8926 2013-09-19 00:00:00 10517 ON_HOLD
9290 2013-09-21 00:00:00 11879 COMPLETE
9793 2013-09-24 00:00:00 9809 COMPLETE
9816 2013-09-24 00:00:00 1753 COMPLETE
14047 2013-10-20 00:00:00 6473 CLOSED 
%%sql

SELECT order_id,
    substr(order_date::varchar, 1, 10) AS order_date, 
    order_customer_id, 
    order_status
FROM orders
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status
1021 2013-07-30 10118 COMPLETE
4068 2013-08-17 12293 PENDING
5881 2013-08-30 3715 CLOSED
7564 2013-09-09 8648 CLOSED
8766 2013-09-18 855 COMPLETE
8926 2013-09-19 10517 ON_HOLD
9290 2013-09-21 11879 COMPLETE
9793 2013-09-24 9809 COMPLETE
9816 2013-09-24 1753 COMPLETE
14047 2013-10-20 6473 CLOSED

Let us understand how to extract the information from the string where there is a delimiter.

  • split_part can be used to split a string using delimiter and extract the information.

  • If there is no data in a given position after splitting, it will be represented as empty string ‘’.

%%sql

SELECT split_part('2013-07-25', '-', 1) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
2013 
%%sql

WITH addresses AS (
    SELECT '593 Fair Oaks Pass, Frankfort, Kentucky, 40618' AS address UNION
    SELECT ', Vancouver, Washington, 98687' UNION
    SELECT '83047 Glacier Hill Circle, Sacramento, California, 94237' UNION
    SELECT '935 Columbus Junction, Cincinnati, Ohio, 45213' UNION
    SELECT '03010 Nevada Crossing, El Paso, Texas, 88579' UNION
    SELECT '9 Dunning Circle, , Arizona, 85271' UNION
    SELECT '96 Fair Oaks Way, Decatur, Illinois, 62525' UNION
    SELECT '999 Caliangt Avenue, Greenville, South Carolina, 29615' UNION
    SELECT '2 Saint Paul Trail, Bridgeport, , 06673' UNION
    SELECT '3 Reindahl Center, Ogden, Utah'
) SELECT split_part(address, ', ', 1) street,
    split_part(address, ', ', 2) city,
    split_part(address, ', ', 3) state,
    split_part(address, ', ', 4) postal_code
FROM addresses
ORDER BY postal_code

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
street city state postal_code
3 Reindahl Center Ogden Utah
2 Saint Paul Trail Bridgeport 06673
999 Caliangt Avenue Greenville South Carolina 29615
593 Fair Oaks Pass Frankfort Kentucky 40618
935 Columbus Junction Cincinnati Ohio 45213
96 Fair Oaks Way Decatur Illinois 62525
9 Dunning Circle Arizona 85271
03010 Nevada Crossing El Paso Texas 88579
83047 Glacier Hill Circle Sacramento California 94237
Vancouver Washington 98687 
%%sql

WITH addresses AS (
    SELECT '593 Fair Oaks Pass, Frankfort, Kentucky, 40618' AS address UNION
    SELECT ', Vancouver, Washington, 98687' UNION
    SELECT '83047 Glacier Hill Circle, Sacramento, California, 94237' UNION
    SELECT '935 Columbus Junction, Cincinnati, Ohio, 45213' UNION
    SELECT '03010 Nevada Crossing, El Paso, Texas, 88579' UNION
    SELECT '9 Dunning Circle, , Arizona, 85271' UNION
    SELECT '96 Fair Oaks Way, Decatur, Illinois, 62525' UNION
    SELECT '999 Caliangt Avenue, Greenville, South Carolina, 29615' UNION
    SELECT '2 Saint Paul Trail, Bridgeport, , 06673' UNION
    SELECT '3 Reindahl Center, Ogden, Utah'
) SELECT split_part(address, ', ', 1) street,
    split_part(address, ', ', 2) city,
    split_part(address, ', ', 3) state,
    split_part(address, ', ', 4) postal_code
FROM addresses
WHERE split_part(address, ', ', 1) = ''
ORDER BY postal_code

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
street city state postal_code
Vancouver Washington 98687 
%%sql

WITH unique_ids AS (
    SELECT '241-80-7115' AS unique_id UNION
    SELECT '694-30-6851' UNION
    SELECT '586-92-5361' UNION
    SELECT '884-65-284' UNION
    SELECT '876-99-585' UNION
    SELECT '831-59-5593' UNION
    SELECT '399-88-3617' UNION
    SELECT '733-17-4217' UNION
    SELECT '873-68-9778' UNION
    SELECT '480-69-032'
) SELECT unique_id,
    substring(unique_id FROM 1 FOR 3) AS unique_id_first3,
    substring(unique_id FROM '....$') AS unique_id_last4,
    CASE WHEN length(split_part(unique_id, '-', 3)) = 4
        THEN split_part(unique_id, '-', 3)
        ELSE 'Invalid'
    END AS unique_id_last
FROM unique_ids
ORDER BY unique_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
unique_id unique_id_first3 unique_id_last4 unique_id_last
241-80-7115 241 7115 7115
399-88-3617 399 3617 3617
480-69-032 480 -032 Invalid
586-92-5361 586 5361 5361
694-30-6851 694 6851 6851
733-17-4217 733 4217 4217
831-59-5593 831 5593 5593
873-68-9778 873 9778 9778
876-99-585 876 -585 Invalid
884-65-284 884 -284 Invalid

Using position or strpos

At times we might want to get the position of a substring in a main string. For example, we might want to check whether email ids have @ in them. We can use functions such as position or strpos.

%%sql 

SELECT position('@' IN 'it@versity.com'),
    position('@' IN 'itversity.com')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
position position_1
3 0 
%%sql 

SELECT strpos('it@versity.com', '@'),
    strpos('itversity.com', '@')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
strpos strpos_1
3 0 
%%sql

WITH email_ids AS (
    SELECT 'bsellan0@yellowbook.com' AS email_id UNION
    SELECT 'rstelljes1@illinois.edu' UNION
    SELECT 'mmalarkey2@webeden.co.uk' UNION
    SELECT 'emussared3@redcross.org' UNION
    SELECT 'livashin4@bloglovin.com' UNION
    SELECT 'gkeach5@cbc.ca' UNION
    SELECT 'emasham6@xing.com' UNION
    SELECT 'rcobbald7@house.gov' UNION
    SELECT 'rdrohan8@washingtonpost.com' UNION
    SELECT 'aebben9@arstechnica.com'
)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.SyntaxError) syntax error at end of input
LINE 12: )
          ^

[SQL: WITH email_ids AS (
    SELECT 'bsellan0@yellowbook.com' AS email_id UNION
    SELECT 'rstelljes1@illinois.edu' UNION
    SELECT 'mmalarkey2@webeden.co.uk' UNION
    SELECT 'emussared3@redcross.org' UNION
    SELECT 'livashin4@bloglovin.com' UNION
    SELECT 'gkeach5@cbc.ca' UNION
    SELECT 'emasham6@xing.com' UNION
    SELECT 'rcobbald7@house.gov' UNION
    SELECT 'rdrohan8@washingtonpost.com' UNION
    SELECT 'aebben9@arstechnica.com'
)]
(Background on this error at: http://sqlalche.me/e/13/f405)

Trimming and Padding Functions

Let us understand how to trim or remove leading and/or trailing spaces in a string.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

  • ltrim is used to remove the spaces on the left side of the string.

  • rtrim is used to remove the spaces on the right side of the string.

  • trim is used to remove the spaces on both sides of the string.

%%sql

SELECT ltrim('     Hello World') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World 
%%sql

SELECT rtrim('     Hello World       ') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World 
%%sql

SELECT length(trim('     Hello World       ')) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
11 
%%sql

SELECT ltrim('----Hello World----', '-') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World---- 
%%sql

SELECT rtrim('----Hello World----', '-') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
----Hello World 
%%sql

SELECT trim('----Hello World----', '-') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World

Let us understand how to use padding to pad characters to a string.

  • Let us assume that there are 3 fields - year, month and date which are of type integer.

  • If we have to concatenate all the 3 fields and create a date, we might have to pad month and date with 0.

  • lpad is used more often than rpad especially when we try to build the date from separate columns.

%%sql

SELECT 2013 AS year, 7 AS month, 25 AS myDate

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
year month mydate
2013 7 25 
%%sql

SELECT lpad(7::varchar, 2, '0') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
07 
%%sql

SELECT lpad(10::varchar, 2, '0') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
10 
%%sql

SELECT lpad(100::varchar, 2, '0') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
10

Reverse and Concatenating multiple strings

Let us understand how to reverse a string as well as concatenate multiple strings.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

  • We can use reverse to reverse a string.

  • We can concatenate multiple strings using concat and concat_ws.

  • concat_ws is typically used if we want to have the same string between all the strings that are being concatenated.

%%sql

SELECT reverse('Hello World') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
dlroW olleH 
%%sql

SELECT concat('Hello ', 'World') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World 
%%sql

SELECT concat('Order Status is ', order_status) AS result
FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
result
Order Status is COMPLETE
Order Status is PENDING
Order Status is CLOSED
Order Status is CLOSED
Order Status is COMPLETE
Order Status is ON_HOLD
Order Status is COMPLETE
Order Status is COMPLETE
Order Status is COMPLETE
Order Status is CLOSED 
%%sql

SELECT * FROM (SELECT 2013 AS year, 7 AS month, 25 AS myDate) q

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
year month mydate
2013 7 25 
%%sql

SELECT concat(year, '-', lpad(month::varchar, 2, '0'), '-',
              lpad(myDate::varchar, 2, '0')) AS order_date
FROM
    (SELECT 2013 AS year, 7 AS month, 25 AS myDate) q

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
order_date
2013-07-25 
%%sql

SELECT concat_ws('-', year, lpad(month::varchar, 2, '0'),
              lpad(myDate::varchar, 2, '0')) AS order_date
FROM
    (SELECT 2013 AS year, 7 AS month, 25 AS myDate) q

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
order_date
2013-07-25

String Replacement

Let us go through the details related to string replacement.

  • replace can be used to replace a sub string with in a string with another string.

  • overlay can be used to replace a sub string with in a string by position with another string.

  • translate can be used to replace individual characters with other characters.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT replace('Hello World', 'alo', 'ello') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World 
%%sql

SELECT overlay('Halo World' PLACING 'ello' FROM 2 FOR 3) AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
Hello World 
%%sql

WITH unique_ids AS (
    SELECT '241-80-7115' AS unique_id UNION
    SELECT '694-30-6851' UNION
    SELECT '586-92-5361' UNION
    SELECT '884-65-2844' UNION
    SELECT '876-99-5856' UNION
    SELECT '831-59-5593' UNION
    SELECT '399-88-3617' UNION
    SELECT '733-17-4217' UNION
    SELECT '873-68-9778' UNION
    SELECT '487-21-9802'
) SELECT unique_id,
    replace(unique_id, '-', ' ') AS unique_id_replaced,
    translate(unique_id, '-', ' ') AS unique_id_translated,
    overlay(unique_id PLACING ' ' FROM 4 FOR 1) AS unique_id_overlaid
FROM unique_ids
ORDER BY unique_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
unique_id unique_id_replaced unique_id_translated unique_id_overlaid
241-80-7115 241 80 7115 241 80 7115 241 80-7115
399-88-3617 399 88 3617 399 88 3617 399 88-3617
487-21-9802 487 21 9802 487 21 9802 487 21-9802
586-92-5361 586 92 5361 586 92 5361 586 92-5361
694-30-6851 694 30 6851 694 30 6851 694 30-6851
733-17-4217 733 17 4217 733 17 4217 733 17-4217
831-59-5593 831 59 5593 831 59 5593 831 59-5593
873-68-9778 873 68 9778 873 68 9778 873 68-9778
876-99-5856 876 99 5856 876 99 5856 876 99-5856
884-65-2844 884 65 2844 884 65 2844 884 65-2844 
%%sql

WITH unique_ids AS (
    SELECT '241-80-7115' AS unique_id UNION
    SELECT '694-30:6851' UNION
    SELECT '586-92-5361' UNION
    SELECT '884:65-2844' UNION
    SELECT '876/99-5856' UNION
    SELECT '831-59:5593' UNION
    SELECT '399-88-3617' UNION
    SELECT '733:17-4217' UNION
    SELECT '873:68-9778' UNION
    SELECT '487-21/9802'
) SELECT unique_id,
    replace(replace(unique_id, '-', ' '), ':', ' ') AS unique_id_replaced,
    translate(unique_id, '-:/', '   ') AS unique_id_translated,
    overlay(overlay(unique_id PLACING ' ' FROM 4 FOR 1) PLACING ' ' FROM 7 FOR 1) AS unique_id_overlaid
FROM unique_ids
ORDER BY unique_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
unique_id unique_id_replaced unique_id_translated unique_id_overlaid
241-80-7115 241 80 7115 241 80 7115 241 80 7115
399-88-3617 399 88 3617 399 88 3617 399 88 3617
487-21/9802 487 21/9802 487 21 9802 487 21 9802
586-92-5361 586 92 5361 586 92 5361 586 92 5361
694-30:6851 694 30 6851 694 30 6851 694 30 6851
733:17-4217 733 17 4217 733 17 4217 733 17 4217
831-59:5593 831 59 5593 831 59 5593 831 59 5593
873:68-9778 873 68 9778 873 68 9778 873 68 9778
876/99-5856 876/99 5856 876 99 5856 876 99 5856
884:65-2844 884 65 2844 884 65 2844 884 65 2844

Note

In case of translate, if we do not have characters for replacement, then those will be replaced with empty string. For example, translate('+86 (238) 954-9649', '+() -', '0')will result in 0862389549649.

%%sql

SELECT translate('+86 (238) 954-9649', '+() -', '0') AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
result
0862389549649 
%%sql

WITH phone_numbers AS (
    SELECT '+86 (238) 954-9649' AS phone_number UNION
    SELECT '+420 (331) 900-5807' UNION
    SELECT '+1 (320) 484-4495' UNION
    SELECT '+45 (238) 961-9801' UNION
    SELECT '+51 (123) 545-6543' UNION
    SELECT '+63 (308) 354-2560' UNION
    SELECT '+86 (433) 851-1260' UNION
    SELECT '+63 (332) 705-0319' UNION
    SELECT '+351 (147) 359-3767' UNION
    SELECT '+57 (714) 557-0468'
) SELECT phone_number, 
    translate(phone_number, '+() -', '') phone_number_int
FROM phone_numbers
ORDER BY phone_number

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
phone_number phone_number_int
+1 (320) 484-4495 13204844495
+351 (147) 359-3767 3511473593767
+420 (331) 900-5807 4203319005807
+45 (238) 961-9801 452389619801
+51 (123) 545-6543 511235456543
+57 (714) 557-0468 577145570468
+63 (308) 354-2560 633083542560
+63 (332) 705-0319 633327050319
+86 (238) 954-9649 862389549649
+86 (433) 851-1260 864338511260
Overview of Pre-Defined Functions Date Manipulation Functions