How to return a Mysql query with specific characters within each cell?

Asked

Viewed 106 times

0

If in my column there is a field in this format: 181202272, (a varchar of 10 characters), I would like to return in my query through a SELECT by Mysql in this way:

18:12:02.272
//Nos dois primeiros caracteres inserir dois pontos, em seguida avança mais dois caracteres e inserir mais :, e antes dos trés últimos inserir um ponto
mysql select varchar

1 answer

3


Good night, my friend, To perform this action just use the command SUBSTR(str, pos, len) from Mysql itself, in this command you must place the string and the desired start and end positions. Ex:

mysql> SELECT SUBSTR('181202272',0,2);
+--------------------------+
| SUBSTR('181202272',0,2)  |
+--------------------------+
| 18                       | 
+--------------------------+
1 row in set (0.01 sec)

You will do for each part of your string that you want to segment. Next using the command CONCAT (string1, string2,…)you must carry out the desired concatenation.

mysql> SELECT CONCAT(SUBSTR('181202272',0,2),':',SUBSTR('181202272',2,2));
+--------------------------------------------------------------+
| CONCAT(SUBSTR('181202272',0,2),':',SUBSTR('181202272',2,2))  |
+--------------------------------------------------------------+
| 18:12                                                        | 
+--------------------------------------------------------------+
1 row in set (0.01 sec)

Perform the CONCAT of all the SUBSTR desired separated by the character you desire.

I hope it helped.

Hugs.

References: REPLACE CONCAT

Browser other questions tagged mysql select varchar

You are not signed in. Login or sign up in order to post.