November 20th, 2012, 04:31 PM
Splitting Text String in to column
I am trying to pull some data from a column named shopsale_delivery in a table named shopsale the issue is that the text in this field is in a string with a new line delimiter ( \n ) and i require each new line to be captured in to different columns named as per the below
just for your reference my previous sql was set up like this until i discovered the field i was referencing for the address columns was incorrect and that the whole address was in one column
ss.shopsale_id as `orderId`,
ss.shopsale_delivery_cost as `fPostageCost`,
ss.shopsale_datetime as `dReceievedDate`,
concat( us.user_firstname,' ' ,us.user_lastname) as 'cFullName',
us.user_email as `cEmailAddress`,
ua.useraddress_postcode as 'cPostCode',
ss.shopsale_cost as `fTotalCharge`,
"" as `cCurrency`,
ss.shopsale_tax as 'fTax',
ua.useraddress_country as 'Country',
ss.shopsale_id as `ReferenceNum`,
ua.useraddress_1 as 'addAddress1',
ua.useraddress_2 as 'addAddress2',
'' as 'addAddress3',
ua.useraddress_city as 'addTown',
ua.useraddress_region as 'addRegion',
ua.useraddress_phone as 'CustomerPhoneNumber',
'' as 'addCompany',
'' as 'BillingCompany',
ua.useraddress_1 as 'BillingAddress1',
ua.useraddress_2 as 'BillingAddress2',
'' as 'Billingaddress3',
ua.useraddress_city as 'BillingTown',
ua.useraddress_Region as 'BillingRegion',
ua.useraddress_country as 'BillingCountry',
ua.useraddress_postcode as 'BillingPostcode',
'Default' as 'PaymentMethod'
FROM shopsale as ss
LEFT OUTER JOIN useraddress as ua
ON ss.shopsale_user_id = ua.useraddress_id
LEFT OUTER JOIN user as us
ON ss.shopsale_user_id = us.user_id
WHERE shopsale_status = 'PAID'
AND ua.useraddress_type = 'Delivery'
Hope someone can help me.
December 2nd, 2012, 07:03 AM
i think that an example is needed here to understand the format of your 'shopsale_delivery' column. i assume it's a text string, but what would an insert for that column look like?