Split text by symbol - Revision history

Unknown user: /* Approach 2: SUBSTRING_INDEX */

2023-09-28T06:29:20Z

Approach 2: SUBSTRING_INDEX

← Older revision		Revision as of 14:29, 28 September 2023
Line 76:		Line 76:

	[https://docs.google.com/spreadsheets/d/11VeZIxGR7orL4smf_0G6oi7VxxNi3FFsCL9b6QrLhBE/edit?usp=sharingc online demo] (allow to edit)		[https://docs.google.com/spreadsheets/d/11VeZIxGR7orL4smf_0G6oi7VxxNi3FFsCL9b6QrLhBE/edit?usp=sharingc online demo] (allow to edit)
	~~<div style="float:left; padding-right:5px;">~~
	~~{{#widget:Iframe~~
	~~\|url=https://docs.google.com/spreadsheet/ccc?key=11VeZIxGR7orL4smf_0G6oi7VxxNi3FFsCL9b6QrLhBE~~
	~~\|width=900~~
	~~\|height=500~~
	~~\|border=1~~
	}}

	=== Split text by symbol in PHP ===		=== Split text by symbol in PHP ===

Unknown user at 06:12, 28 September 2023

2023-09-28T06:12:03Z

← Older revision		Revision as of 14:12, 28 September 2023
Line 1:		Line 1:
	Split text by symbol (another string, also called delimiter or separator)		Split text by symbol (another string, also called delimiter or separator)

			== Example data ==
			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

	== Approaches ==		== Approaches ==
Line 7:		Line 14:
	* PHP: [http://php.net/explode explode] or [http://php.net/manual/en/function.preg-split.php preg_split] function		* PHP: [http://php.net/explode explode] or [http://php.net/manual/en/function.preg-split.php preg_split] 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~~ ===		=== Split text by symbol in MySQL ===
	{{exclaim}} Only one symbol was allowed. The following query will get the last element if there are more than two symbols.		{{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>))}}
Line 28:		Line 29:
	</pre>		</pre>

	=== Excel ~~Approach~~ ===		=== Split text by symbol in Excel ===
	Comparison of approaches		Comparison of approaches
	<table border="1" class="wikitable sortable">		<table border="1" class="wikitable sortable">
Line 83:		Line 84:
	}}		}}

	=== PHP ~~Approach~~ ===		=== Split text by symbol in PHP ===
	PHP script		PHP script
	<pre>		<pre>

Unknown user: /* Approach 1: Pparse data */

2020-07-20T07:37:46Z

Approach 1: Pparse data

← Older revision		Revision as of 15:37, 20 July 2020
Line 45:		Line 45:
	</table>		</table>

	==== Approach 1: ~~Pparse~~ data ====		==== Approach 1: Parse data ====
	[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])		[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])
	* instruction with screenshots: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]		* instruction with screenshots: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]

Planetoid: /* Approach 2: SUBSTRING_INDEX */

2017-11-15T05:25:32Z

Approach 2: SUBSTRING_INDEX

← Older revision		Revision as of 13:25, 15 November 2017
Line 65:		Line 65:
	</table>		</table>
	If the data set is perfect		If the data set is perfect
			# separator character: ,
	# to get the first string ( 25.040215 ): B1 = {{code \| code=<span style="background-color: #F6CEF5">MID( A1, 1, FIND(",", A1)-1)</span> }}		# to get the first string ( 25.040215 ): B1 = {{code \| code=<span style="background-color: #F6CEF5">MID( A1, 1, FIND(",", A1)-1)</span> }}
	# to get the second string ( 121.512532 ): C1 = {{code \| code=<span style="background-color: #ccc">MID( A1, FIND(",", A1)+1, LEN(A1))</span> }}		# to get the second string ( 121.512532 ): C1 = {{code \| code=<span style="background-color: #ccc">MID( A1, FIND(",", A1)+1, LEN(A1))</span> }}

Planetoid: /* Excel Approach */

2017-11-15T05:24:18Z

Excel Approach

@@ Line 29: / Line 29: @@
 === Excel Approach ===
-==== Approach 1: parse data ====
+Comparison of approaches
 [https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])
 * instruction with screenshots: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]

Planetoid at 22:44, 14 November 2017

2017-11-14T22:44:57Z

← Older revision		Revision as of 06:44, 15 November 2017
Line 31:		Line 31:
	==== Approach 1: parse data ====		==== Approach 1: parse data ====
	[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])		[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])
	* instruction with ~~images~~: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]		* instruction with screenshots: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]

	==== Approach 2: SUBSTRING_INDEX ====		==== Approach 2: SUBSTRING_INDEX ====

Planetoid: /* Excel Approach */

2017-11-14T13:37:32Z

Excel Approach

← Older revision		Revision as of 21:37, 14 November 2017
Line 29:		Line 29:

	=== Excel Approach ===		=== Excel Approach ===
	==== 1: parse data ====		==== Approach 1: parse data ====
	[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])		[https://support.microsoft.com/en-us/kb/214261 parse data] ([https://support.microsoft.com/zh-tw/kb/214261 資料剖析])
	* instruction with images: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]		* instruction with images: [https://support.office.com/en-za/Article/split-text-into-different-cells-30b14928-5550-41f5-97ca-7a3e9c363ed7 Split text into different cells - Excel]

	==== 2: SUBSTRING_INDEX ====		==== Approach 2: SUBSTRING_INDEX ====
	<table border="1" style="">		<table border="1" style="">
	<tr style="background-color: #555555; color: #ffffff;">		<tr style="background-color: #555555; color: #ffffff;">

Planetoid: /* Example */

2016-11-08T04:15:03Z

Example

@@ Line 65: / Line 65: @@
 |border=1
 }}
 [[Category:Data transformation]]

Planetoid: /* Approaches */

2016-11-08T04:02:47Z

Approaches

← Older revision		Revision as of 12:02, 8 November 2016
Line 5:		Line 5:
	* 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		* PHP: [http://php.net/explode explode] or [http://php.net/manual/en/function.preg-split.php preg_split] function

	== Example ==		== Example ==

Planetoid: /* MySQL Approach */

2015-11-04T10:09:31Z

MySQL Approach

← Older revision		Revision as of 18:09, 4 November 2015
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>