Clever Geek Handbook

Insert seperator string into field value in MySQL

I have this value generated using MAX and LPAD

Result: enter image description here

I would like to put a dash after every third number like 000-000-002 , but I don't know what to do. Please help me.

Thank you for the advanced!

+3


source to share


4 answers


use php function wordwrap()

to put a specific character after the number of characters. The function wordwrap()

wraps the line to new lines when it reaches a certain length.

$output = wordwrap($orNumber,3,'-',true);


Note. This function can leave white spaces at the beginning of a line. Thus, you have to trim your data to remove spaces.

You can check the mannual here PHP wordwrap ()

+2


source


MySQL:

set @var ='000000002';
select  CONCAT_WS('-', SUBSTR(@var , 1, 3), SUBSTR(@var, 4,3), SUBSTR(@var, 7))


This will only accept varchar. For int values, digits starting with 0 will not give the correct error. Hence, it is recommended to handle PHP side.

+1


source 


set @chr = '000000002';

select @chr,
         concat(substring(@chr,1,3),'-',substring(@chr,4,3),'-',substring(@chr,7,9)) hyphenated

Result

+-----------+-------------+
| @chr      | hyphenated  |
+-----------+-------------+
| 000000002 | 000-000-002 |
+-----------+-------------+
1 row in set (0.00 sec)
+1


source


if the length is always fixed , you can use LEFT(), MID()

andRIGHT() 

update TABLE_NAME set orNumber = CONCAT(LEFT(orNumber, 3), '-' , MID(orNumber, 4, 3), '-' ,RIGHT(orNumber, 3))

find more on the docs

+1


source






More articles:


All Articles