How to format a string value in MySQL?

Question

Say there is a varchar column account_id all of them are 10 integer like 1234567890. How can I format a value like 1234567890 to 123-456-7890 in mysql?


1234567890  => 123-456-7890

ysth · Accepted Answer · 2020-06-21 06:55:38Z

1

concat(
    substring(account_id,1,3),
    '-',
    substring(account_id,4,3),
    '-',
    substring(account_id,7,4)
)

answered Jun 21, 2020 at 6:55

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jia Jimmy Over a year ago

I've tried with CONCAT(LEFT(account_id, 3),'-',LEFT(RIGHT(account_id, 7),3),'-', RIGHT(account_id, 4)). Could you please give me some advice about which one is better on execution considering?

ysth Over a year ago

I doubt there's a noticable difference. That will definitely be different if the value is longer or shorter than 10.

Slava Rozhnev · Accepted Answer · 2020-06-21 09:36:53Z

1

You also can use CONCAT_WS function:

SELECT CONCAT_WS('-',
    LEFT(account_id, 3), -- first 3 symbols
    MID(account_id, 4, LENGTH(account_id)-7), -- rest middle symbols
    RIGHT(account_id, 4) -- last 4 symbols
);

answered Jun 21, 2020 at 9:36

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to format a string value in MySQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related