CODEWITHSUNDEEP
mysqlstring-formattingstring-function
jia Jimmy
jia Jimmy

Reputation: 1848

How to format a string value in MySQL?

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

Upvotes: 0

Views: 1229

Answers (2)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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
);

Upvotes: 1

ysth
ysth

Reputation: 98388

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

Upvotes: 1

Related Questions