Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0

    Replace and concat


    Hello There

    Thank you for taking time to read this post.

    I have an application I use to import a csv file from suppliers. The application processes the file then

    uploads to a shopping cart products.

    The application allows you to edit files using mysql make changes to it before upload. I want to change

    the categories names in the file to my own ones- on the fly rather than having to import into excel first

    change manually then use the edited csv file, and then add other categories names to that replaced value.

    Bascially I want to know correct way of using REPLACE and CONCAT together.

    For example a column 6 in supplier files represent the suppliers Categories.

    REPLACE([CSV_COL(6)],'Notebook','Laptop')

    this correctly changes all category Names in supplier file from Notebook to Laptop

    secondly

    CONCAT("Default Category|Computers|",[CSV_COL(6)])

    This adds
    Default Category|Computers in front of whatever the value is in column 6 which carrying on from the

    example above would be Notebook

    So final category location would be

    Default Category|Computers|Notebook

    BUT I am trying to get it to add the REPLACED value at the end so it looks like this.

    Default Category|Computers|Laptop

    Please can anyone explain how I would achieve this

    join these 2 together or any other way to achieve what i want to do.

    REPLACE([CSV_COL(6)],'Notebook','Laptop')

    CONCAT("Default Category|Computers|",[CSV_COL(6)])

    Regards
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    i would first like to point out that square brackets are not legal in mysql, you must use backticks instead

    try this --
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(`CSV_COL(6)`,'Notebook','Laptop')
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    i would first like to point out that square brackets are not legal in mysql, you must use backticks instead

    try this --
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(`CSV_COL(6)`,'Notebook','Laptop')
          )
    Thank you for your kind reply, I dont know but the appliction I am using only works with [] around any columns references.

    I got it to work using this

    CONCAT('Default Category|Computers|', REPLACE([CSV_COL(6)],'Notebook','Laptop'))

    However I can not add any more categories correctly i have lots I dont understand how to do this correctly,

    CONCAT('Default Category|Computers|', REPLACE([CSV_COL(6)],'Notebook','Laptop'))

    REPLACE([CSV_COL(6)],'Storage','Hard Drive')

    I tired adding at the bottom using comma etc
    what is the correct way and easiest for replacing alot of categories please.

    Regards
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by nrt453
    Thank you for your kind reply, I dont know but the appliction I am using only works with [] around any columns references.
    weird, weird, weird

    Originally Posted by nrt453
    what is the correct way and easiest for replacing alot of categories please.
    store the replacements in a translation table, and do a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    weird, weird, weird

    store the replacements in a translation table, and do a join
    Thank you for the fast reply, much appreciated,
    the thing is that this is not a stored database or tables, it is basically desktop application used to import and edit csv files on the fly then upload to website/server, database.

    So I can not call any tables.

    I am sure it is straightforward but I can complete novice and it's like struggling with a new language, I am not getting the correct syntax -

    Any help would be most appreciated.

    Been struggling with it last 3 days.

    Kind Regards
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by nrt453
    it is basically desktop application used to import and edit csv files on the fly then upload to website/server, database.
    i understand completely, because i once had exactly the same job

    basically, you have to learn to write functions -- what they do, how to invoke them, how to nest them, fixing syntax errors when you have unbalanced parentheses, and so on

    you are on the right track so keep going and remember to study the manual with diligence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    i understand completely, because i once had exactly the same job

    basically, you have to learn to write functions -- what they do, how to invoke them, how to nest them, fixing syntax errors when you have unbalanced parentheses, and so on

    you are on the right track so keep going and remember to study the manual with diligence
    Thank you for the reply and encouragement I seem to have made some progress,

    Using
    CONCAT('Default Category|Computers|', REPLACE([CSV_COL(6)],'Notebookl','Laptop'), REPLACE([CSV_COL(6)],'Storage','HardDrive')
    )


    However after this is processed both the previous value and new replaced values are shown together joined up. Obviously I dont want the value that i want to be replacing i.e. The first value shown in the examples below e.g. Notebook to be shown aswell and I can figure out why it showing both, for example

    NotbookLaptop
    StorageHardDrive

    Please can someone check and let me know how to do it correctly as I can not find examples or tutorials for this type of questions having spent days searching.

    Thank you once again
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    i gave you a hint -- the REPLACE functions need to be nested
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(
            REPLACE(
            REPLACE([CSV_COL(6)],'Notebookl','Laptop')
                                ,'Storage','HardDrive')
                                ,'Cassette','TapeDrive')
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    i gave you a hint -- the REPLACE functions need to be nested
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(
            REPLACE(
            REPLACE([CSV_COL(6)],'Notebookl','Laptop')
                                ,'Storage','HardDrive')
                                ,'Cassette','TapeDrive')
          )
    THANK YOU THANK YOU

    Great working fine now, I even managed to get it to work with another REPLACE so now it changes the categories from 2 columns then adds them to the predefined ones. Works beautifully

    Can not thank you enough for your help and guidance very overwhelming and really really appreciated.

    KIND REGARDS
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by nrt453
    THANK YOU THANK YOU

    Great working fine now, I even managed to get it to work with another REPLACE so now it changes the categories from 2 columns then adds them to the predefined ones. Works beautifully

    Can not thank you enough for your help and guidance very overwhelming and really really appreciated.

    KIND REGARDS
    Sorry to keep this post going but I have hit another problem I can not solve, been trying over last 2 days following on from the example above I have come across some categories where the suppliers category names has ' in it for example

    Cassette's

    The single comma ' inside the category name is getting me syntax error, this is how it looks in the expression.

    CONCAT( 'Default Category|Computers|'
    , REPLACE(
    REPLACE(
    REPLACE([CSV_COL(6)],'Notebookl','Laptop')
    ,'Storage','HardDrive')
    ,'Cassette's','TapeDrive')
    )


    I have tried adding a REPLACE just for the ' so it searches column 6 and replaces any ' in category names with space or blank.

    I read about being able to add chr(39) in sql tried this way too but nothing seems to work.

    I have managed to process the statement without getting syntax errors however, the category name does not get replaced after the expression is processed.

    Any help on how to do this Please as its the only thing preventing me from finishing of this piece - taken so many hours and days over this.

    Regards
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    standard sql (also supported by mysql) for embedded single quotes is to code two of them in a row --
    Code:
    , 'Cassette''s' , 'TapeDrive')
    and i should like to point out that whoever decided to use an apostrophe to denote a plural is, let's be kind, misguided
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    standard sql (also supported by mysql) for embedded single quotes is to code two of them in a row --
    Code:
    , 'Cassette''s' , 'TapeDrive')
    and i should like to point out that whoever decided to use an apostrophe to denote a plural is, let's be kind, misguided
    Thank you once again for your kind reply, really appreciate it

    I think I already tried the way you have explained but still get syntax error.

    I forgot to add that there is another word after the 's Acc

    CONCAT( 'Default Category|Computers|'
    , REPLACE(
    REPLACE(
    REPLACE([CSV_COL(6)],'Notebookl','Laptop')
    ,'Storage','HardDrive')
    ,'Cassette's Acc,'TapeDrive')
    )

    so Cassette's Acc

    I too dont understand why they have done this makes life complicated

    I have tried amongst other things

    ,'Cassett'''s' Acc','TapeDrive')

    Any other suggestions please?

    Regards
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by nrt453
    Thank you once again for your kind reply, really appreciate it

    I think I already tried the way you have explained but still get syntax error.

    I forgot to add that there is another word after the 's Acc

    CONCAT( 'Default Category|Computers|'
    , REPLACE(
    REPLACE(
    REPLACE([CSV_COL(6)],'Notebookl','Laptop')
    ,'Storage','HardDrive')
    ,'Cassette's Acc,'TapeDrive')
    )

    so Cassette's Acc

    I too dont understand why they have done this makes life complicated

    I have tried amongst other things

    ,'Cassett'''s' Acc','TapeDrive')

    Any other suggestions please?

    Regards
    I have managed to get it working now. The issue was with me trying to use a second REPLACE in the same expression to replace category names from another Column.

    So column 6 are the main category names and column 11 are sub category names are.

    CONCAT( 'Default Category|Computers|'
    , REPLACE(
    REPLACE(
    REPLACE([CSV_COL(6)],'Notebookl','Laptop')
    ,'Storage','HardDrive')
    , 'Cassette''s' , 'TapeDrive'),

    REPLACE([CSV_COL(11)],'TDK','|''TDK-90')

    )

    It use to work before I removed it when I couldnt get it to work with 's - so now that works i added this second replace back in and now i get syntax error.

    So carrying on from the example above the correct output should be

    Default Category|Computers|TapeDrive|TDK-90

    Tapedrive being the replaced value from column 6 and TDK-90 being the replaced value for column 11. The | is needed to form the categories correctly.

    Any suggestions on this please would be most appreciated, like im taking one step forward and 2 steps back.

    Regards
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by nrt453
    ... and now i get syntax error.
    you need to be able to concentrate on counting commas and parentheses, and concatenating the right things in the right sequence

    it's easy!

    it would help if you got in the habit of indenting, that makes the errors more obvious
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(
            REPLACE(
            REPLACE( [CSV_COL(6)]
                   , 'Notebookl' , 'Laptop' )
                   , 'Storage' , 'HardDrive' )
                   , 'Cassette''s' , 'TapeDrive' )
          , '|'
          , REPLACE( [CSV_COL(11)]
                   , 'TDK' , 'TDK-90' )
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    you need to be able to concentrate on counting commas and parentheses, and concatenating the right things in the right sequence

    it's easy!

    it would help if you got in the habit of indenting, that makes the errors more obvious
    Code:
    CONCAT( 'Default Category|Computers|'
          , REPLACE(
            REPLACE(
            REPLACE( [CSV_COL(6)]
                   , 'Notebookl' , 'Laptop' )
                   , 'Storage' , 'HardDrive' )
                   , 'Cassette''s' , 'TapeDrive' )
          , '|'
          , REPLACE( [CSV_COL(11)]
                   , 'TDK' , 'TDK-90' )
          )

    Thank you for your answer.

    I tried doing it like this but still comes up with syntax error. In the editor similar to dreamwever screen it highlights parts of the expression black blue etc I know it wont work when the values needed to be check and replaced are black, only when they are blue, this what makes it more difficult to understand, why I get syntax errors when they are highlighted blue text.

    Can it be anything else?

    Thank you once again
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo