Thread: Concat Help

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

    Join Date
    Dec 2011
    Posts
    44
    Rep Power
    7

    Concat Help


    Hello

    I am using an application which can use mysql to manipulate a csv file - please note this may be not the orthodox mysql methods and ways which are applied however uses same logic.
    I have a 8 columns in the csv files with images url for products

    which i am trying to Concat and use a character ; to separate them

    i am also using a Replace to change the url names in the csv columns to different url


    This is what i have now

    REPLACE(
    CONCAT (

    [CSV_COL(24)] ';'
    [CSV_COL(25)], ';'
    [CSV_COL(26)], ';'
    [CSV_COL(27)], ';'
    [CSV_COL(28)], ';'
    [CSV_COL(29)], ';'
    [CSV_COL(30)], ';'
    [CSV_COL(31)]),


    'https://oldurl.com/washing_machine','http://www.newurl.com/washing_machine'

    )

    This works however some products have 1 images some have 2 or 3 or 4 or 5 or 6 - or 7 or 8
    and the cells in the csv files for these products are blank
    this gives me output results like this

    ;;';;;';


    'http://www.newurl.com/washing_machine;;';;;';'

    'http://www.newurl.com/washing_machine;[url]http://www.newurl.com/cooker;;';;;';'

    at the end of rows for products with less than the 8 images
    I am trying trim however i am struggling with the syntax as i mentioned the application runs on what is says is MYSQL expressions and is based on same MYSQL logic so i have to do trial and error

    Can anyone suggest a way of removing ;;';;;';

    Or is there an alternative way to concat to say

    If [CSV_COL(24)] is not empty then Concat with [CSV_COL(25)]
    Thank you for taking the time to read this spent 2 days on this but no joy
    Last edited by nrt453; November 17th, 2017 at 02:50 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,743
    Rep Power
    4288
    Code:
    REPLACE(CONCAT(CASE WHEN [CSV_COL(24)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(24)],';') END 
                  ,CASE WHEN [CSV_COL(25)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(25)],';') END 
                  ,CASE WHEN [CSV_COL(26)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(26)],';') END 
                  ,CASE WHEN [CSV_COL(27)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(27)],';') END 
                  ,CASE WHEN [CSV_COL(28)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(28)],';') END 
                  ,CASE WHEN [CSV_COL(29)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(29)],';') END 
                  ,CASE WHEN [CSV_COL(30)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(30)],';') END 
                  ,CASE WHEN [CSV_COL(31)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(31)],';') END 
                  )
           ,'https://oldurl.com/washing_machine'
           ,'http://www.newurl.com/washing_machine'
           )

    Comments on this post

    • nrt453 agrees : great thank you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    44
    Rep Power
    7
    Originally Posted by r937
    Code:
    REPLACE(CONCAT(CASE WHEN [CSV_COL(24)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(24)],';') END 
                  ,CASE WHEN [CSV_COL(25)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(25)],';') END 
                  ,CASE WHEN [CSV_COL(26)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(26)],';') END 
                  ,CASE WHEN [CSV_COL(27)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(27)],';') END 
                  ,CASE WHEN [CSV_COL(28)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(28)],';') END 
                  ,CASE WHEN [CSV_COL(29)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(29)],';') END 
                  ,CASE WHEN [CSV_COL(30)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(30)],';') END 
                  ,CASE WHEN [CSV_COL(31)] = '' THEN ''
                        ELSE CONCAT([CSV_COL(31)],';') END 
                  )
           ,'https://oldurl.com/washing_machine'
           ,'http://www.newurl.com/washing_machine'
           )
    Thank you for your kind reply, rudy this works beautifully - i have also been able to change it a little to work for other uses too - which is perfect big thank you much appreciated

IMN logo majestic logo threadwatch logo seochat tools logo