【MySQL】拆分经纬度字段

 

数据结构：

表中一字段存储经度和纬度：

+---------------------------+
| INSTALL_LOLA_NUM          |
+---------------------------+
| 23.0345735213,69.01224234 |
| 23.0340313,69.01224234    |
| 93.03453,69.01224234      |
| 10.23683,69.01224234      |
| 43.0345123,69.01224234    |
+---------------------------+

查询时，将其拆分成两个字段，经度和纬度

 

SQL解决办法：

使用 INSTR + RIGHT + LEFT + LENGTH 函数组合

SELECT 
    COL, -- 原始字段
    INSTR(COL, ',') AS IDX, --  逗号下标
    LEFT(COL, INSTR(COL, ',') - 1) AS LEFT_CUT, -- 左切部分
    RIGHT(COL, LENGTH(COL) - INSTR(COL, ',')) AS RIGHT_CUT -- 右切部分
FROM TABLE

案例效果：

+---------------------------+-----+---------------+-------------+
| C                         | IDX | LEFT_CUT      | RIGHT_CUT   |
+---------------------------+-----+---------------+-------------+
| 23.0345735213,69.01224234 |  14 | 23.0345735213 | 69.01224234 |
| 23.0340313,69.01224234    |  11 | 23.0340313    | 69.01224234 |
| 93.03453,69.01224234      |   9 | 93.03453      | 69.01224234 |
| 10.23683,69.01224234      |   9 | 10.23683      | 69.01224234 |
| 43.0345123,69.01224234    |  11 | 43.0345123    | 69.01224234 |
| 6                         |   0 |               | 6           |
| 7                         |   0 |               | 7           |
| 8                         |   0 |               | 8           |
| 9                         |   0 |               | 9           |
| 10                        |   0 |               | 10          |
| 11                        |   0 |               | 11          |
| 12                        |   0 |               | 12          |
| 13                        |   0 |               | 13          |
| 14                        |   0 |               | 14          |
| 15                        |   0 |               | 15          |
| 16                        |   0 |               | 16          |
| 17                        |   0 |               | 17          |
| 18                        |   0 |               | 18          |
| 19                        |   0 |               | 19          |
| 20                        |   0 |               | 20          |
| 21                        |   0 |               | 21          |
| 22                        |   0 |               | 22          |
| 23                        |   0 |               | 23          |
| 24                        |   0 |               | 24          |
+---------------------------+-----+---------------+-------------+

发现新的问题，右切会全切，在加一个判断，左切的判断

SELECT 
    C,
    INSTR(C, ',') AS IDX,
    LEFT(C, INSTR(C, ',') - 1) AS LEFT_CUT,
    IF(LEFT(C, INSTR(C, ',') - 1) , RIGHT(C, LENGTH(C) - INSTR(C, ',')), '') AS RIGHT_CUT
FROM A

效果：

+---------------------------+-----+---------------+-------------+
| C                         | IDX | LEFT_CUT      | RIGHT_CUT   |
+---------------------------+-----+---------------+-------------+
| 23.0345735213,69.01224234 |  14 | 23.0345735213 | 69.01224234 |
| 23.0340313,69.01224234    |  11 | 23.0340313    | 69.01224234 |
| 93.03453,69.01224234      |   9 | 93.03453      | 69.01224234 |
| 10.23683,69.01224234      |   9 | 10.23683      | 69.01224234 |
| 43.0345123,69.01224234    |  11 | 43.0345123    | 69.01224234 |
| 6                         |   0 |               |             |
| 7                         |   0 |               |             |
| 8                         |   0 |               |             |
| 9                         |   0 |               |             |
| 10                        |   0 |               |             |
| 11                        |   0 |               |             |
| 12                        |   0 |               |             |
| 13                        |   0 |               |             |
| 14                        |   0 |               |             |
| 15                        |   0 |               |             |
| 16                        |   0 |               |             |
| 17                        |   0 |               |             |
| 18                        |   0 |               |             |
| 19                        |   0 |               |             |
| 20                        |   0 |               |             |
| 21                        |   0 |               |             |
| 22                        |   0 |               |             |
| 23                        |   0 |               |             |
| 24                        |   0 |               |             |
+---------------------------+-----+---------------+-------------+

 

解决方案参考此博客：

https://www.cnblogs.com/liyue-sqsf/p/9077249.html

 

同样，使用LEFT + RIGHT 函数 可以实现 电话号脱敏

set @phone = 13566778899;

select case 
when length(@phone) = 11 then 
concat(left(@phone,3),'****',right(@phone,4))
else @phone end as phone;