DELIMITER $$
BEGIN
declare next int;
declare all_len int;
declare pre VARCHAR(100);
declare lon VARCHAR(20);
declare lat VARCHAR(20);
declare lon_before int;
declare lon_after int;
declare lat_before int;
declare lat_after int;
declare ret VARCHAR(1000);
set next=1;
SET all_len = (length(in_str) - length(replace(in_str,'|',''))) + 1;
# 循环遍历 S0503, W05318|S0508, W05107|S0635, W05114|S0653, W05313|S0503, W05318
while next <= all_len DO
set pre = REPLACE(SUBSTRING(SUBSTRING_INDEX(in_str, '|', next), LENGTH(SUBSTRING_INDEX(in_str, '|', next -1)) + 1), '|', '');
set lon = REPLACE(SUBSTRING(SUBSTRING_INDEX(pre, ',', 1), LENGTH(SUBSTRING_INDEX(pre, ',', 1 -1)) + 1), ',', '');
set lat = REPLACE(SUBSTRING(SUBSTRING_INDEX(pre, ',', 2), LENGTH(SUBSTRING_INDEX(pre, ',', 2 -1)) + 1), ',', '');
# INSERT INTO aaa (tt) VALUES (pre);
IF locate('E', lon) >= 1 THEN
set lon = REPLACE(lon,'E',''); # E0503 -> 0530
set lon_after=cast(RIGHT(lon, 2) as UNSIGNED INTEGER); # 后两位 地理坐标的分 即 0530中的30
IF length(lon) = 2 THEN
set lon_before = 0;
ELSE
set lon_before = cast(left(lon,length(lon)-2) as UNSIGNED INTEGER);
END IF;
set lon = round(lon_before + lon_after/60, 2);
ELSEIF locate('W', lon) >= 1 THEN
set lon = REPLACE(lon,'W','');
set lon_after=cast(RIGHT(lon, 2) as UNSIGNED INTEGER);
IF length(lon) = 2 THEN
set lon_before = 0;
ELSE
set lon_before = cast(left(lon,length(lon)-2) as UNSIGNED INTEGER);
END IF;
set lon = -round(lon_before + lon_after/60, 2);
ELSEIF locate('N', lon) >= 1 THEN
set lon = REPLACE(lon,'N','');
set lon_after=cast(RIGHT(lon, 2) as UNSIGNED INTEGER);
IF length(lon) = 2 THEN
set lon_before = 0;
ELSE
set lon_before = cast(left(lon,length(lon)-2) as UNSIGNED INTEGER);
END IF;
set lon = round(lon_before + lon_after/60, 2);
ELSEIF locate('S', lon) >= 1 THEN
set lon = REPLACE(lon,'S','');
set lon_after=cast(RIGHT(lon, 2) as UNSIGNED INTEGER);
IF length(lon) = 2 THEN
set lon_before = 0;
ELSE
set lon_before = cast(left(lon,length(lon)-2) as UNSIGNED INTEGER);
END IF;
set lon = -round(lon_before + lon_after/60, 2);
END IF;
IF locate('E', lat) >= 1 THEN
set lat = LTrim(REPLACE(lat,'E',''));
set lat_after=cast(RIGHT(lat, 2) as UNSIGNED INTEGER);
IF length(lat) = 2 THEN
set lat_before = 0;
ELSE
set lat_before = cast(left(lat,length(lat)-2) as UNSIGNED INTEGER);
END IF;
set lat = round(lat_before + lat_after/60, 2);
ELSEIF locate('W', lat) >= 1 THEN
set lat = LTrim(REPLACE(lat,'W',''));
set lat_after=cast(RIGHT(lat, 2) as UNSIGNED INTEGER);
IF length(lat) = 2 THEN
set lat_before = 0;
ELSE
set lat_before = cast(left(lat,length(lat)-2) as UNSIGNED INTEGER);
END IF;
set lat = -round(lat_before + lat_after/60, 2);
ELSEIF locate('N', lat) >= 1 THEN
set lat = LTrim(REPLACE(lat,'N',''));
set lat_after=cast(RIGHT(lat, 2) as UNSIGNED INTEGER);
IF length(lat) = 2 THEN
set lat_before = 0;
ELSE
set lat_before = cast(left(lat,length(lat)-2) as UNSIGNED INTEGER);
END IF;
set lat = round(lat_before + lat_after/60, 2);
ELSEIF locate('S', lon) >= 1 THEN
set lat = LTrim(REPLACE(lat,'S',''));
set lat_after=cast(RIGHT(lat, 2) as UNSIGNED INTEGER);
IF length(lat) = 2 THEN
set lat_before = 0;
ELSE
set lat_before = cast(left(lat,length(lat)-2) as UNSIGNED INTEGER);
END IF;
set lat = -round(lat_before + lat_after/60, 2);
END IF;
IF next = 1 THEN
set ret = CONCAT(lon, ',', lat);
ELSE
set ret = CONCAT(ret, '|', lon, ',', lat);
END IF;
set next=next+1;
END WHILE;
RETURN ret;
END