Split text by symbol: Difference between revisions
Jump to navigation
Jump to search
m (→Approaches) |
|||
| Line 3: | Line 3: | ||
== Approaches == | == Approaches == | ||
list of equivalent approaches to split text by symbol | list of equivalent approaches to split text by symbol | ||
* MySQL: [http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php MySQL SUBSTRING_INDEX() function - w3resource] | * MySQL: [http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php MySQL SUBSTRING_INDEX() function - w3resource] | ||
* Excel: (1) [https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析]) {{exclaim}} only allowed one character used as separator! (2) LEFT, RIGHT, FIND, LEN (3) MOD, FIND, LEN | * Excel: (1) [https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析]) {{exclaim}} only allowed one character used as separator! (2) LEFT, RIGHT, FIND, LEN (3) MOD, FIND, LEN | ||
* PHP: [http://php.net/explode explode] function | |||
== Example == | == Example == | ||
Revision as of 22:00, 23 September 2015
Split text by symbol (another string, also called delimiter or separator)
Approaches
list of equivalent approaches to split text by symbol
- MySQL: MySQL SUBSTRING_INDEX() function - w3resource
- Excel: (1) parse data (資料剖析)
only allowed one character used as separator! (2) LEFT, RIGHT, FIND, LEN (3) MOD, FIND, LEN - PHP: explode function
Example
Task: Split the position e.g. 25.040215, 121.512532 by the comma ( , ) to obtain the latitude (1st string) and longitude (2nd string).
Expected output:
- 25.040215
- 121.512532
MySQL Approach
- to get the first string ( 25.040215 ):
SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', 1)) - to get the second string ( 121.512532 ):
SELECT TRIM(SUBSTRING_INDEX('25.040215, 121.512532' ,',', -1))
CREATE TABLE tbl(str varchar(50));
INSERT INTO tbl VALUES('25.040215, 121.512532');
SELECT TRIM(SUBSTRING_INDEX(str ,',', 1)), TRIM(SUBSTRING_INDEX(str ,',', -1)) FROM tbl;
Excel Approach
1: parse data
2: SUBSTRING_INDEX
| A | B | C | |
|---|---|---|---|
| 1 | 25.040215, 121.512532 | 25.040215 |
121.512532 |
If the data set is perfect
- to get the first string ( 25.040215 ): B1 =
MID( A1, 1, FIND(",", A1)-1) - to get the second string ( 121.512532 ): C1 =
MID( A1, FIND(",", A1)+1, LEN(A1))
if the data set was mixed with empty values. Return empty if the value is empty.
- to get the first string ( 25.040215 ): B1 =
IF(ISERROR(MID(A1, 1, FIND(",", A1)-1)), "", MID(A1, 1, FIND(",", A1)-1)) - to get the second string ( 121.512532 ): C1 =
IF(ISERROR(TRIM(MID(A1, FIND(",", A1)+1, LEN(A1)))), "", TRIM(MID(A1, FIND(",", A1)+1, LEN(A1)))
online demo (allow to edit)