将’7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km‘ 用sql按“^”拆分多个字段
时间: 2024-06-18 11:05:19 浏览: 44
可以使用MySQL中的SUBSTRING_INDEX函数来实现按“^”拆分多个字段,具体的SQL语句如下:
```
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 1),'^',-1) AS field1,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 2),'^',-1),'^',1) AS field2,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 3),'^',-1),'^',1) AS field3,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 4),'^',-1),'^',1) AS field4,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 5),'^',-1),'^',1) AS field5,
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX('7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km', '^', 6),'^',-1),'^',1) AS field6;
```
以上SQL语句将会把“7123091961542447^宽带-新装^a15198710044^292993^15287112028^tangtiankui.km”字符串拆分成六个字段,每个字段以“^”为分隔符。其中,第一个字段为field1,第二个字段为field2,以此类推。你可以根据需要修改以上SQL语句。