Thread: Remove url text

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

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7

    Remove url text


    Hello

    Thank you for taking the time to read this.

    I have spent many hours on this now and run out of ideas

    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.

    In the past kind members have offered my solutions which i applied and they worked or i made some adjustments to make it work with the app that i have

    for current issue im stuck with,


    All I need to do is remove https://media.dfs.com/asset/en/media/ if itís there

    And do nothing if its blank.

    If itís blank do we still need to tell it itís blank as using the '' in replace gives database syntax error

    Same thing if I use '' ''

    If I go into the csv file inside Open Office or Excel and remove the https://media.dfs.com/asset/en/media/ manually and save the csv - then import it there are no Database Errors Ė even if fields are blank

    However there are 25,000 products and files will be updated frequently so need to automate this step inside the app that im using.

    In trying but my syntax or logic is failing or maybe both.

    Would really appreciate if anyone can help me out

    More details if needed



    I have csv file which i'm importing using the app

    in it are columns (for example Column 24 ) for url links to pdf file for products for example

    https://media.dfs.com/asset/en/media/user manual cb1411.pdf


    Not all products have pdf files and if they dont then the field inside the csv is blank

    So the way we import is by simply download the pdf and then upload to our server and at import time we just need the file name

    So using the example url above we only need
    user manual cb1411.pdf

    1. Now I can use a replace

    REPLACE([CSV_COL(24)],
    " https://media.dfs.com/asset/en/media/user manual cb1411.pdf",
    "")

    And it gives me correct result
    user manual cb1411.pdf

    However the problem is when there is no value after import im getting database errors

    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 '))

    2. I have tried many different ways using CASE WHEN and


    REPLACE((CASE WHEN [CSV_COL(24)] = ' https://media.dfs.com/asset/en/media/ ' THEN ''
    ELSE ([CSV_COL(24)]) END
    )
    ,'https://media.nisbets.com/asset/en/media/'
    ,''
    )


    And this too gives me correct value

    user manual cb1411.pdf


    from my understanding this is replacing only if Column 24 has https://media.dfs.com/asset/en/media/ and doing nothing if it doesnít.


    However this too gives me same database error.


    PS please can somone also let me know how to remove the clickable url links in the post i have tried adding // and also <> "<>" but the blue links are still showing in this post
    Last edited by nrt453; March 31st, 2018 at 05:40 PM.
  2. #2
  3. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,673
    Rep Power
    1841
    Do you get a similar error when that column in the CSV is blank when you import it, without trying any REPLACEs (i.e, just do a 'plain' import straight)? Is the database seeing blank columns as NULLs?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7
    Originally Posted by SimonJM
    Do you get a similar error when that column in the CSV is blank when you import it, without trying any REPLACEs (i.e, just do a 'plain' import straight)? Is the database seeing blank columns as NULLs?
    thank you for your kind reply,

    If i open the csv file in say open office or excel and go to those columns and remove the url part myself with find and replace, save it and try to import it.

    then there are no problems and no errors. it recognizes the filenames and blank spaces without any replace all i have to do is map the csv column to the one i need it for on the database.

    However there are 25000+ products and they are split into 10 separate csv files. so this means we would have to repeat these steps each time we need to import fresh data which is very time consuming


    I have managed to do a lot more complicated things to manipulate these files in the past with help of some kind members, and also manage to adapt there solutions and advice for other places however i cant believe why its so hard to achieve just removing the url part in the column

    i have also tried reverse substring following a guide however it also uses a replace - which again brings up the error

    thanks
  6. #4
  7. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,673
    Rep Power
    1841
    Just for fun, what is the actual error message you are getting?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7
    Originally Posted by SimonJM
    Just for fun, what is the actual error message you are getting?
    The error messages are like this

    Part 1. Import products...
    Product ID [14027]: Product was modified
    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 ')), ('14027', '4', '154', '1', ()), ('14027', '4', '87', '0', '/c/f/cf917_cf918.' at line 1

    I need to find a way for it to either understand any blank cells in the csv file are empty - do nothing

    or check cell and then if it has certain value ie https://media.dfs.com/asset/en/media/ THEN replace


    however most things i have tried so far keep bringing the syntax error - even though syntax check prior to the import doesn't show anything - the application has feature to build and check the expressions - this database error is showing after import and resulting in those products not importing and also any products that come after the first product with the issue. Very strange
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,792
    Rep Power
    4331
    this --
    Code:
    ()
    is invalid

    try using the keyword NULL (no quotes) instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7
    Originally Posted by r937
    this --
    Code:
    ()
    is invalid

    try using the keyword NULL (no quotes) instead


    thank you for the reply

    Im getting a different error

    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 ')), ('7375', '4', '85', '1', ()), ('7375', '4', '109', '0', 'container2') ON DUP' at line 1


    Im using similar example from online just to see how replace and case works together and try to fit into what i want this time check if csv column if empty then NULL else replace the url

    so if column 24 is blank / empty then NULL otherwise do the replace


    REPLACE(

    CASE
    WHEN [CSV_COL(24)] = '' THEN NULL
    ELSE REPLACE (
    'https://www.oldsite.com', 'http://www.newsite.com'

    )

    However mine is not properly formed so is not executing to test

    my result should be
    Null if there is nothing in cell or
    http://www.newsite/com/tsh1.jpg

    the one they use in example is

    SELECT
    REPLACE ('Hello', 'e', '!'),
    REPLACE(
    CASE
    WHEN 1 = 1 THEN 'Hello'
    ELSE 'Bye'
    END, 'e', '!'
    )

    although theirs executes without errors the result is all the e is replaced by ! which is not exactly what we want so in mine i tried to remove what i dont need, also i only need the filename or image name that comes after the url in the final result so using above example

    if inside the csv file its like this

    https://www.oldsite.com/tsh1.jpg

    then if not empty final result should be

    https://www.newsite.com/tsh1.jpg
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7
    another way im attempting is

    REPLACE(
    CASE
    WHEN [CSV_COL(25)] = '' THEN NULL
    ELSE replace ([CSV_COL(25)], "www.oldsite.com", "www.newsite.com") END

    end as [CSV_COL(25)]


    but im getting syntax error

    check right syntax to use near line 6

    can anyone help me to complete the expression
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,792
    Rep Power
    4331
    Originally Posted by nrt453
    but im getting syntax error
    you need only one REPLACE
    Code:
    CASE WHEN [CSV_COL(25)] = '' 
         THEN NULL 
         ELSE REPLACE( [CSV_COL(25)], "www.oldsite.com", "www.newsite.com") 
         END 
     AS [CSV_COL(25)]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    50
    Rep Power
    7
    Originally Posted by r937
    you need only one REPLACE
    Code:
    CASE WHEN [CSV_COL(25)] = '' 
         THEN NULL 
         ELSE REPLACE( [CSV_COL(25)], "www.oldsite.com", "www.newsite.com") 
         END 
     AS [CSV_COL(25)]
    Thank you very much for your kind reply,

    this works and if its empty result it null is shown during testing which is correct behavior.

    However now i realise the problem is that even with other ones i tried and which executed without errors during testing i.e. not syntax error however after import i get the syntax errors . Reason is my database is not allowing NULL for strange reason until 1 month ago any replace or anything i was running with
    REPLACE([CSV_COL(24)], "www.oldsite.com/", "http://www.newsite.com/") was importing without any errors

    however now even after using the correct method with case when NULL any resulting NULL is not accepted and error is shown after import,


    If i use 'NULL' or any other text in case its empty the website will create links to pdf and images which don't exist, which is not what i want as extra 1000's of links would be created
    I checked last couple of days and only thing i found which can allow blanks or empty entries is by inside my.cnf

    sql-mode="" below [mysqld] in my.cnf (on Linux)

    i tired this and then restarted mysql server and tried to import however it still not accepting any resulting NULL values whatsoever

    im really baffled by this because in the phpmyadmin i can see NULL value is ticked please see attached

    so im wondering if there is any other setting in mysql or php which can be causing this issue.

    As i said I have been using same expressions in the app all this time without any error but even after changing them to include NULL if blank i am now getting errors.

    Anyone provide any further help with this would be most appreicated

    Regards
    Attached Images

IMN logo majestic logo threadwatch logo seochat tools logo