#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Splitting Text String in to column


    Hi,

    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

    'addAddress1',
    'addAddress2',
    'addAddress3',
    'addTown',
    'addRegion',
    'cPostCode'

    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

    SELECT
    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.

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    92
    Rep Power
    3
    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?

IMN logo majestic logo threadwatch logo seochat tools logo