14,954
edits
Split text by symbol: Difference between revisions
Jump to navigation
Jump to search
→MySQL Approach
| Line 15: | Line 15: | ||
=== MySQL Approach === | === MySQL Approach === | ||
{{exclaim}} Only one symbol was allowed. The following query will get the last element if there are more than two symbols. | |||
# to get the first string ( 25.040215 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">1</span>))}} | # to get the first string ( 25.040215 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">1</span>))}} | ||
# to get the second string ( 121.512532 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">-1</span>))}} | # to get the second string ( 121.512532 ): {{code | code=SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', <span style="color:red">-1</span>))}} | ||
| Line 22: | Line 23: | ||
CREATE TABLE tbl(str varchar(50)); | CREATE TABLE tbl(str varchar(50)); | ||
INSERT INTO tbl VALUES('25.040215, 121.512532'); | INSERT INTO tbl VALUES('25.040215, 121.512532'); | ||
INSERT INTO tbl VALUES('123.456'); /*bad data*/ | INSERT INTO tbl VALUES('25.040215, 121.512532, 999.999'); /*bad data: 2 or more symbols*/ | ||
INSERT INTO tbl VALUES('123.456'); /*bad data: no symbol exists*/ | |||
SELECT TRIM(SUBSTRING_INDEX(str ,',', 1)), TRIM(SUBSTRING_INDEX(str ,',', -1)) FROM tbl WHERE str LIKE '%,%'; | SELECT TRIM(SUBSTRING_INDEX(str ,',', 1)), TRIM(SUBSTRING_INDEX(str ,',', -1)) FROM tbl WHERE str LIKE '%,%'; | ||
</pre> | </pre> | ||