Jump to content

HOW TO SELECT A DELIMITED FIELD AS MULTIPLE FIELDS


Recommended Posts

hi all,i've got a semicolon delimited text_Consumer field in the table 6_serviceleads.this field contains the user information, delimited by ; eg:

Zubair Vally;zubair.vally@gijima.com;012 675 7548;Cape Town

all the fields have these values: the consumer's name, the consumer's e-mail, the consumer's telephone number and the consumer's city / town.i need to do something like follows:

SELECT SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',0) AS text_ConsumerName, SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS `text_ConsumerE-mail`, SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2) AS text_ConsumerTel, SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3) AS text_ConsumerCityTown FROM 6_serviceleads ORDER BY text_ConsumerName ASC;

but i know this would not work (this is not how SUBSTRING_INDEX works), i can't seem to find any reference for a "split" function in mysql. :/please help with this!

Edited by Pierre 'Greywacke' du Toit
Link to post
Share on other sites

okay nevermind :)lol i just had to do a double SUBSTRING_INDEX on some of values:

SELECT 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS text_ConsumerName, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2),';',-1) AS `text_ConsumerE-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3),';',-1) AS text_ConsumerTel, 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',-1) AS text_ConsumerCityTown FROM 6_serviceleads ORDER BY text_ConsumerName ASC;

this issue has now been resolved :)

Link to post
Share on other sites

okay this is the sql query i have sofar, to be exported as CSV through PMA.

SELECT 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS text_ConsumerName, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2),';',-1) AS `text_ConsumerE-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3),';',-1) AS text_ConsumerTel, 	1_regions.text_RegionDescription AS text_ConsumerRegion, 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',-1) AS text_ConsumerCityTown, 	5_serviceleads.timestamp_LeadCreated, 	UNKNOWN_FUNCTION(6_serviceleads.tinyint_LeadSent) AS boolean_LeadSent,	REPLACE(6_serviceleads.text_LeadAttributes,'<br />','\r\n') AS text_LeadAttributes, 	5_suppliers.text_SupplierName, 	2_servicescatalogue.text_ServiceDescriptionFROM 6_serviceleads LEFT JOIN 1_regions ON (	6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID} LEFT JOIN 2_servicescatalogue ON (	6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY text_ConsumerName ASC;

i would now like to know which function should i use to convert the tinyint values (either 0 or 1) to "TRUE" or "FALSE" for this select.anybody know? i've searched myself moeg and i cannot find much except for useless essays on mysql only supporting tinyint and not boolean values, tinyint vs enum('true', 'false') etc.i searched for MYSQL tinyint to boolean amongst others... :/

Edited by Pierre 'Greywacke' du Toit
Link to post
Share on other sites

okay, thanks justsomeguy - the statement looks as follows:

SELECT 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS text_ConsumerName, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2),';',-1) AS `text_ConsumerE-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3),';',-1) AS text_ConsumerTel, 	1_regions.text_RegionDescription AS text_ConsumerRegion, 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',-1) AS text_ConsumerCityTown, 	6_serviceleads.timestamp_LeadCreated, 	IF(6_serviceleads.tinyint_LeadSent = 1,'TRUE','FALSE') AS boolean_LeadSent, 	REPLACE(6_serviceleads.text_LeadAttributes,'<br />','\r\n') AS text_LeadAttributes, 	5_suppliers.text_SupplierName, 	2_servicescatalogue.text_ServiceDescription FROM 6_serviceleads LEFT JOIN 1_regions ON (	6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID} LEFT JOIN 2_servicescatalogue ON (	6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY text_ConsumerName ASC;

unfortunately PMA reports the following:

MySQL said: Documentation#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}LEFT JOIN 2_servicescatalogueON ( 6_serviceleads.bigint_ServiceID = 2_serv' at line 20
Link to post
Share on other sites

well duh. lol the problem was a } instead of a ) by accident :)here is the working code ^^

SELECT 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',1) AS text_ConsumerName, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',2),';',-1) AS `text_ConsumerE-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',3),';',-1) AS text_ConsumerTel, 	1_regions.text_RegionDescription AS text_ConsumerRegion, 	SUBSTRING_INDEX(6_serviceleads.text_Consumer,';',-1) AS text_ConsumerCityTown, 	6_serviceleads.timestamp_LeadCreated, 	IF(6_serviceleads.tinyint_LeadSent = 1,'TRUE','FALSE') AS boolean_LeadSent, 	REPLACE(6_serviceleads.text_LeadAttributes,'<br />','\r\n') AS text_LeadAttributes, 	5_suppliers.text_SupplierName, 	2_servicescatalogue.text_ServiceDescription FROM 6_serviceleads LEFT JOIN 1_regions ON (	6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID) LEFT JOIN 2_servicescatalogue ON (	6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY text_ConsumerName ASC;

issue is RESOLVED!! (776 records returned) 8Dnow to do the prospecting leads as opposed to the active leads... 0o

Edited by Pierre 'Greywacke' du Toit
Link to post
Share on other sites

okay have finished the prospecting leads select statement:

SELECT 	REPLACE(10_serviceprospects.text_LeadAttributes,'<br />','\r\n') AS `Lead Attributes`, 	SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',1) AS `Consumer Name`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',2),';',-1) AS `Consumer E-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',3),';',-1) AS `Consumer Telephone`, 	1_regions.text_RegionDescription AS `Consumer Region`, 	SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',-1) AS `Consumer City/Town`, 	13_prospectleadsent.timestamp_Sent AS `Lead Created`, 	IF((		SELECT 13_prospectleadsent.bigint_ProspectID		FROM 13_prospectleadsent 		WHERE 13_prospectleadsent.bigint_ProspectID = 10_serviceprospects.bigint_ProspectID	) > 0,'TRUE','FALSE') AS `Lead Sent`, 	5_suppliers.text_SupplierName AS `Supplier`, 	2_servicescatalogue.text_ServiceDescription AS `Service` FROM 10_serviceprospects LEFT JOIN 13_prospectleadsent ON ( 	10_serviceprospects.bigint_ProspectID = 13_prospectleadsent.bigint_ProspectID) LEFT JOIN 1_regions ON (	10_serviceprospects.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	13_prospectleadsent.bigint_SupplierID = 5_suppliers.bigint_SupplierID) LEFT JOIN 2_servicescatalogue ON (	10_serviceprospects.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY `Lead Created` DESC;

but PMA returns the following error:

MySQL said: Documentation#1109 - Unknown table '2_servicescatalogue' in field list
now why would this be? this table is specified on a LEFT JOIN...the field names were made more readable for the nonsql people, in the active leads selection statement too - as here.why would this statement not be joining the 2_servicescatalogue table? Edited by Pierre 'Greywacke' du Toit
Link to post
Share on other sites

ah the solution was EXISTS :)

SELECT 	REPLACE(10_serviceprospects.text_LeadAttributes,'<br />','\r\n') AS `Lead Attributes`, 	SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',1) AS `Consumer Name`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',2),';',-1) AS `Consumer E-mail`, 	SUBSTRING_INDEX(SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',3),';',-1) AS `Consumer Telephone`, 	1_regions.text_RegionDescription AS `Consumer Region`, 	SUBSTRING_INDEX(10_serviceprospects.text_Consumer,';',-1) AS `Consumer City/Town`, 	13_prospectleadsent.timestamp_Sent AS `Lead Created`, 	IF(EXISTS (		SELECT 13_prospectleadsent.bigint_ProspectID		FROM 13_prospectleadsent 		WHERE 13_prospectleadsent.bigint_ProspectID = 10_serviceprospects.bigint_ProspectID	),'TRUE','FALSE') AS `Lead Sent`, 	5_suppliers.text_SupplierName AS `Supplier`, 	2_servicescatalogue.text_ServiceDescription AS `Service` FROM 10_serviceprospects LEFT JOIN 13_prospectleadsent ON ( 	10_serviceprospects.bigint_ProspectID = 13_prospectleadsent.bigint_ProspectID) LEFT JOIN 1_regions ON (	10_serviceprospects.bigint_RegionID = 1_regions.bigint_RegionID) LEFT JOIN 5_suppliers ON (	13_prospectleadsent.bigint_SupplierID = 5_suppliers.bigint_SupplierID) LEFT JOIN 2_servicescatalogue ON (	10_serviceprospects.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID) ORDER BY `Lead Created` DESC;

this query is now ready for CSV export :)

Link to post
Share on other sites
  • 1 year later...

I am sure that this post must have taught many people to select a delimited field as multiple fields which is so much more useful and with all the details given, I am sure that it would be easy for anyone to understand it!! It is so interesting to read the discussions here and I love to be around in this forum as there is so much to learn from everybody here!! Hope to see more of these in future too!!.........................vCloud Connector

Edited by hellenk
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...