Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Remove two plus sign from phone number field in SQL

I want to remove two plus(+) sign from phone number field and add just one plus sign.

For eg: The phone number can be in any of these formats:

(+) (+911) 24234324324324
++091787534 5303
(++91) 9711931220`

Desired Output:

(+911) 24234324324324
+091787534 5303
(+91) 9711931220`

Below is the query that I am using

**replace((TRIM(LEADING '0' FROM (REGEXP_REPLACE(channel_value, '[]\[!@#$%.&*~^_{}:;<>/\|()+-]', '')))),' ' ,'')**

Waiting for your suggestions.

Thank you.

Tags:

2

Answers


  1. 0

    To achieve your desired output in SQL, you can use a combination of string manipulation functions and regular expressions. Here’s how you can modify your query:

    SELECT 
    CASE 
        -- If phone number starts with two '+' signs, remove one
        WHEN channel_value LIKE '++%' THEN
            CONCAT('+', SUBSTRING(channel_value, 3))
        -- If phone number starts with a single '+' sign, keep as is
        WHEN channel_value LIKE '+%' THEN
            channel_value
        -- If phone number doesn't start with a '+', add one at the beginning
        ELSE
            CONCAT('+', channel_value)
    END AS normalized_phone_number
FROM
    your_table_name;

    This query checks the format of the phone number in the channel_value column. If it starts with two ‘+’ signs, it removes one. If it starts with a single ‘+’ sign, it keeps it as is. If it doesn’t start with a ‘+’, it adds one at the beginning. This way, you achieve your desired output format directly in SQL.

    Login or Signup to reply.
  2. 0

    replace this pattern: (()?(?:+)?s?(?){2}

    with: $1+

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search