0

I have a column with phone numbers that are in string format due to the fact that there might be special characters in the middle. I already cleaned it up, and now I would like to add "55" to the beginning of the string in case there isn't already.

My query:

SELECT leads.name as "lead's name",
       NULLIF(regexp_replace(leads.phone, '\D','','g'), '') as "lead's phone"

FROM leads

ORDER BY leads.created_at DESC

Observation 1: It is indifferent to me whether the result is in number format or string format.

Observation 2: It is a query on Metabase. I believe to be the most recent version of PostgreSQL.

Observation 3: I found a similar question that helps adding the "55" to the beginning of the string, but there is no check to see whether there already is a "55" in the beginning of the string. How to add string at the beginning of a string, if it is missing or wrong

Thanks!!

Erick
  • 103
  • 6
  • 1
    `UPDATE tablename SET colname = '55' || colname WHERE colname NOT LIKE '55%'` ??? – Jonathan Willcock Sep 30 '21 at 12:51
  • Thanks @JonathanWillcock! Sorry, but I didn't understand how i would apply that to what I need. On the select, there is "lead's phone", and I would like to add "55" to the beginning of each lead's phone in case there isn't already. – Erick Sep 30 '21 at 14:35
  • 1
    Same principle but with a case statement. `SELECT CASE WHEN "lead's phone" LIKE '55%' THEN "lead's phone" ELSE '55' || "lead's phone" END as "lead's phone" .. ` – Jonathan Willcock Oct 01 '21 at 06:25
  • Sorry that i didn't understand at first sight, I got it now. Thanks @JonathanWillcock for taking the time to answer me twice. :) – Erick Oct 06 '21 at 18:34

0 Answers0