CODEWITHSUNDEEP
sqlregexpostgresql
Andrius
Andrius

Reputation: 21188

Postgresql - remove any whitespace from sub string

How can I remove any white space from substring of string?

For example I have this number '+370 650 12345'. I need all numbers to have this format country_code rest_of_the_number or in that example: +370 65012345. How could you achieve that with PostgreSQL?

I could use trim() function, but then it would remove all whitespace.

Upvotes: 2

Views: 6399

Answers (3)

David Faber
David Faber

Reputation: 12495

The following will work even if the country code is absent (see SQL Fiddle Demo here):

SELECT TRIM(REPLACE(REPLACE(REGEXP_REPLACE('+370 650 12345', '^((\+\d+)\s+)?(.*)$', '\1|\3'), ' ', ''), '|', ' '));

Returns: +370 65012345

SELECT TRIM(REPLACE(REPLACE(REGEXP_REPLACE('370 650 12345', '^((\+\d+)\s+)?(.*)$', '\1|\3'), ' ', ''), '|', ' '));

Returns: 37065012345

It looks for a country code (a set of numbers starting with a + sign) at the beginning, and replaces any whitespace following that code with a pipe |. It then replaces all the spaces in the resulting string with the empty string, then replaces occurrences of | with spaces! The choice of | is arbitrary, I suppose it could be any non-digit character.

Upvotes: 0

user330315
user330315

Reputation:

Assuming the column is named phone_number:

left(phone_number, strpos(phone_number, ' '))
   ||regexp_replace(substr(phone_number, strpos(phone_number, ' ') + 1), ' ', '', 'g')

It first takes everything up to the first space and then concatenates it with the result of replacing all spaces from the rest of the string.

If you also need to deal with other whitespace than just a space, you could use '\s' for the search value in regexp_replace()

Upvotes: 2

rchang
rchang

Reputation: 5246

If you are able to assume that a country code will always be present, you could try using a regular expression to capture the parts of interest. Assuming that your phone numbers are stored in a column named content in a table named numbers, you could try something like the following:

SELECT parts[1] || ' ' || parts[2] || parts[3]
FROM (
  SELECT
    regexp_matches(content, E'^\\s*(\\+\\d+)\\s+(\\d+)\\s+(\\d+)\\s*$') AS parts
  FROM numbers
) t;

Upvotes: 0

Related Questions