Greywacke Posted April 22, 2010 Share Posted April 22, 2010 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! Link to comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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 comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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... :/ Link to comment Share on other sites More sharing options...
justsomeguy Posted April 22, 2010 Share Posted April 22, 2010 On option would be to use an if statement instead of a conversion function. Link to comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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 comment Share on other sites More sharing options...
justsomeguy Posted April 22, 2010 Share Posted April 22, 2010 ON ( 6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID}LEFT JOIN 2_servicescatalogue ON ( Link to comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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 Link to comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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 listnow 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? Link to comment Share on other sites More sharing options...
Greywacke Posted April 22, 2010 Author Share Posted April 22, 2010 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 comment Share on other sites More sharing options...
hellenk Posted May 27, 2011 Share Posted May 27, 2011 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.