How to format and insert a character in between a string- Postgresql

postgresql insert character into string
postgres substring
insert character between string sql
postgres find second occurrence in string
postgresql substring between two characters
postgres format
postgresql split string
postgres format string

I have this table.

member_id | phone_number -----------+-------------- 1 | 09151234567 2 | 09904897851 3 | 09196332111 4 | 09957935412 5 | 09251279633 6 | 09357489412 7 | 09155974684 (7 rows)

I format the phone_number column to this format (63)xxxxxxxxx. Using this code update member set phone_number = regexp_replace(phone_number, '0(.)', '(63)');

member_id | phone_number -----------+--------------- 1 | (63)151234567 2 | (63)904897851 3 | (63)196332111 4 | (63)957935412 5 | (63)251279633 6 | (63)357489412 7 | (63)155974684 (7 rows) And now I need to update this column again to this format (63)xxx-xxxxxx. I don't want to replace a character, I need to insert a character in between the string. I've tried using substring, regexp_replace but failed. I'm doing something wrong. I did find questions somehow related to this but they are all about string replacement.

You can do that in a few ways, I like this one with right() and left():

update member 
set phone_number = format('%s-%s', left(phone_number, 7), right(phone_number, -7));

Documentation: 9.3: String Functions and Operators, Strings in this context include values of the types character, character varying, and text. For other cases, insert an explicit coercion to text if you need to duplicate the previous behavior. text, Format arguments according to a format string. Unlike the standard C function sprintf, PostgreSQL 's format function allows format specifiers with and without position fields to be mixed in the same format string. A format specifier without a position field always uses the next argument after the last argument consumed.

To convert from format (63)xxxxxxxxx to (63)xxx-xxxxxx, you could try

UPDATE member SET phone_number = regexp_replace(phone_number, '(\(\d{2}\)\d{3})(\d{6})', '\1-\2');

You could also convert directly from original format to (63)xxx-xxxxxx by using the following

UPDATE member SET phone_number = regexp_replace(phone_number, '(0\d)(\d{3})(\d{6})', '(63)\2-\3');

I've create a sample at this link http://rextester.com/VCX4636

Documentation: 9.1: Character Types, Both of these types can store strings up to n characters (not bytes) in length. for type character varying(5) INSERT INTO test2 VALUES ('too long'::varchar(5));  Format arguments based on a format string: FORMAT(‘Hello %s’,’PostgreSQL’) ‘Hello PostgreSQL’ INITCAP: Convert words in a string to title case: INITCAP(‘hI tHERE’) Hi There: LEFT: Return the first n character in a string: LEFT(‘ABC’,1) ‘A’ LENGTH: Return the number of characters in a string: LENGTH(‘ABC’) 3: LOWER

You can also utilize lookbehinds.

select regexp_replace('(63)xxxxxxxxx','(?<=.{7})','-')

This is particularly useful when there is complex expression instead of phone_number and hence it is not desirable for readability to use it repetitively in multiple calls of left(), right() or substring() function.

PostgreSQL Character Types: CHAR, VARCHAR, And TEXT, If a string casts to a char(n) or varchar(n) explicitly, PostgresQL will truncate the string to n characters before inserting into the table. The text data type can store a​  TO_CHAR(expression, format) Arguments. The PostgreSQL TO_CHAR() function requires two arguments: 1) expression. The expression can be a timestamp, an interval, an integer, a double precision, or a numeric value that is converted to a string according to a specific format. 2) format. The format for the result string.

PostgreSQL INSERT, This tutorial shows you how to insert new rows into a table by using the PostgreSQL INSERT statement. If you want to insert a string that contains a single quote character The date format is YYYY-MM-DD . SELECT · ORDER BY · SELECT DISTINCT · WHERE · LIMIT · FETCH · IN · BETWEEN · LIKE  The following statement uses the FORMAT () function to format a string: SELECT FORMAT ('Hello, %s','PostgreSQL'); The output is: 'Hello, PostgreSQL'. In this example, %s is replaced by the 'PostgreSQL' string argument. See the following customer table in the sample database.

Adding Data with INSERT and COPY, The INSERT SQL command initiates an insertion of data into the table called table_name. These values may be expressions themselves (e.g., an operation between two are delimited by a uniform symbol, or in PostgreSQL's binary table format. file will be treated as blank string constants, rather than NULL, by default). However, one exception is that if the excess characters are all spaces, PostgreSQL will truncate the spaces to the maximum length and store the string. If a string casts to a char(n) or varchar(n) explicitly, PostgresQL will truncate the string to n characters before inserting into the table.

Documentation: 9.1: String Functions and Operators, Strings in this context include values of the types character, character varying, and text. For other cases, insert an explicit coercion to text if you need to duplicate the decode(string text, format text), bytea, Decode binary data from textual  The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character. This option is not allowed when using binary format. NULL. Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string

Comments
  • If you have that correct format, why don't you just use substring(phone_number from 1 for 7) || '-' || substring(phone_number from 8) ?
  • I didn't used substring like that. Yours is right.