CODEWITHSUNDEEP
mysqlsql
Madhu Ch
Madhu Ch

Reputation: 74

Add hyphen in a string after every 2 characters using in mysql select statement

I have a mysql table with some data. In that i have a column(mobile_number) and that column value is like mobile number (ex:1234587920).

my expected out put is : 12-34-58-79-20

I want to add hyphen after every two numbers.

Upvotes: 3

Views: 3900

Answers (5)

Vahid Alvandi
Vahid Alvandi

Reputation: 612

you can use substr and contact_ws command in mysql

update tmp_pay SET   date_fix  = concat_ws('-',substr(date_at,1,4),substr(date_at,5,6),'01') ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would definitely use concat_ws() for this:

select concat_ws('-',
                 substr(mobile_number, 1, 2),
                 substr(mobile_number, 3, 2),
                 substr(mobile_number, 5, 2),
                 substr(mobile_number, 7, 2)
                )

Here is a db<>fiddle.

Upvotes: 3

esnkrimi
esnkrimi

Reputation: 160

use concat() for joining strings and use substr() for split a string in mysql :

split (column,start,count) select column from start position for count charachter

concat(column1,"-",column2) will be column1-column2

SELECT CONCAT(SUBSTR(phone,1,2),'-',SUBSTR(phone,3,2),'-',SUBSTR(phone,5,2),'- 
',SUBSTR(phone,7,2),'-',SUBSTR(phone,9,2)) FROM `table`

Upvotes: 4

Nikhil
Nikhil

Reputation: 3950

this will work:

SELECT concat(SUBSTRING("1234587920", 1, 2),"-",SUBSTRING("1234587920",3,2),"- 
",SUBSTRING("1234587920", 5, 2),"-",
SUBSTRING("1234587920", 7, 2),"-",
SUBSTRING("1234587920", 9, 2)) AS ExtractString;

for your table query is like :

SELECT concat(SUBSTRING(mobile_number, 1, 2),"- 
",SUBSTRING(mobile_number,3,2),"-",SUBSTRING(mobile_number, 5, 2),"-",
SUBSTRING(mobile_number, 7, 2),"-",
SUBSTRING(mobile_number, 9, 2)) ExtractString from tablename;

Upvotes: 0

A.Mujeeb
A.Mujeeb

Reputation: 51

you can try this 

select concat( right(phone_number,3) , "-" , mid(phone_number,4,3) , "-", right(phone_number,4)) from table

Upvotes: -1

Related Questions