Post reply

Insert a suffix to the values

  • January 14, 2016 at 12:14 pm

    #399572

    Hi everyone,

    I have searched and searched for an answer to this, and I think this must be child's play for anyone who KNOWS SQL (which is not me).

    I want to insert a suffix to the values in a field of a table in my DB.

    More specifically, I have a table 'categories' in which I have a field 'categories_image' (which contains the titles of the content items of oscommerce site).

    All the values in this field 'categories_image' are names of individuals. Now all I want to do is add a suffix '.gif' to all the values of this field.

    I can do this from phpmyadmin by clicking the edit pencil icon and simply adding '.gif' in front of all the values but I have about 750 rows and an SQL command which can insert a suffix of '.gif' in front of all values of this field will be a great help.

    I have read about the 'UPDATE' commands but that REPLACES the value with what you provide. But I want to let the values remain and add a suffix before them.

    Please can anyone help me achieve this with a SQL command ?

    Thanks a ton.

  • January 14, 2016 at 12:43 pm

    #1852045

    You need to use an update. Yes, update replaces the value, so make the new value contain the old.

    UPDATE SomeTable

    SET SomeColumn = SomeColumn + 'Another string value'

    btw, this is a site for Microsoft SQL Server, not MySQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2016 at 1:20 pm

    #1852058

    I try this but that's not works..

  • January 14, 2016 at 1:58 pm

    #1852078

    Ok I find it...

    UPDATE `categories_images` SET`categories_image`=concat(trim(`categories_image`),'.gif');

    Now, I would to know if a need to reverse the process... do you have a idea?

    Thanks

  • January 14, 2016 at 2:14 pm

    #1852087

    Restore a backup you took before the update.

    Or, find whatever MySQL function lets you take substrings and use that to remove the last 4 characters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2016 at 2:19 pm

    #1852089

    ..... oops ....


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 14, 2016 at 2:48 pm

    #1852110

    If a need to remove the '.gif' for all the field on the table....

  • January 14, 2016 at 2:51 pm

    #1852111

    guy 1966 (1/14/2016)

    If a need to remove the '.gif' for all the field on the table....

    You should be able to do that with a simple UPDATE statement.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 15, 2016 at 12:49 am

    #1852167

    guy 1966 (1/14/2016)

    If a need to remove the '.gif' for all the field on the table....

    I already answered that

    GilaMonster (1/14/2016)

    find whatever MySQL function lets you take substrings and use that to remove the last 4 characters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 15, 2016 at 6:30 am

    #1852230

    Thank you for your help,

    Actually I tried it on a backup I had.:-D In the process I try to installed, I need to repeat the process in reverse once made my update on my products to allow my oscommerce of worked.

    All this in a perspective of automated update my product. That's why I need to repeat the process in reverse after I update my products .... 😉

    Thank you

  • January 15, 2016 at 6:48 am

    #1852233

    Ok, I finally found what I needed

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'.gif','');

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'','');

    Thank you!

  • January 15, 2016 at 9:34 am

    #1852281

    guy 1966 (1/15/2016)

    Ok, I finally found what I needed

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'.gif','');

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'','');

    Thank you!

    @guy1966, I am not sure why you insisted on getting an answer for a MySQL question from a SQL Server Forum but happy you got something working.

    For those looking at this answer, to be clear, the code above only works on MySQL not SQL Server.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 15, 2016 at 10:41 am

    #1852300

    Orlando Colamatteo (1/15/2016)

    guy 1966 (1/15/2016)

    Ok, I finally found what I needed

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'.gif','');

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'','');

    Thank you!

    @guy1966, I am not sure why you insisted on getting an answer for a MySQL question from a SQL Server Forum but happy you got something working.

    For those looking at this answer, to be clear, the code above only works on MySQL not SQL Server.

    It will also change all the '.gif' occurances in the string, not only the ones defining the extension.

    For example: The string 'proper.gifts.for.weddings.gif' will be changed into 'properts.for.weddings'.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 15, 2016 at 11:34 am

    #1852311

    Luis Cazares (1/15/2016)

    Orlando Colamatteo (1/15/2016)

    guy 1966 (1/15/2016)

    Ok, I finally found what I needed

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'.gif','');

    UPDATE `categories_images` SET `categories_image`=REPLACE (`categories_image`,'','');

    Thank you!

    @guy1966, I am not sure why you insisted on getting an answer for a MySQL question from a SQL Server Forum but happy you got something working.

    For those looking at this answer, to be clear, the code above only works on MySQL not SQL Server.

    It will also change all the '.gif' occurances in the string, not only the ones defining the extension.

    For example: The string 'proper.gifts.for.weddings.gif' will be changed into 'properts.for.weddings'.

    Good catch. I didn't even evaluate the code marked as the solution taking it on face that because the OP marked it that it was good to go. One would be better off adding a WHERE-clause and using SUBSTRING instead of REPLACE:

    UPDATE `categories_images`

    SET `categories_image`= SUBSTRING(`categories_image`,1,LENGTH(`categories_image`)-4)

    WHERE `categories_image` LIKE '%.gif';

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 15, 2016 at 12:28 pm

    #1852320

    Indeed, I tried this code and it works fine. I agree with you it will prevent possible error. Thank you so much

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply