Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 7 of 7

Thread: Find and replace text string in dbo.product

  1. 05-19-2009, 09:59 AM #1
    sprogg is offline Member
    Join Date
    Jan 2009
    Posts
    79

    Default Find and replace text string in dbo.product

    I have discovered that we have some additional text in some of our product desciptions and dont want to go through the whole db (over 1000 items) to see where they are.

    Is there a simple way of finding and replacing the text string with blank, ie " "

    Thank you
  2. 05-19-2009, 01:16 PM #2
    jsimacek is offline Senior Member
    Join Date
    Dec 2008
    Location
    Phoenix, AZ
    Posts
    373

    Default

    Sure, but make a backup first!!!

    UPDATE Product
    SET Description = REPLACE(CONVERT(nvarchar(MAX),Description),'OLDTEX T','NEW TEXT')
    Jan Simacek - Compunix, LLC
    AspDotNetStorefront trusted Devnet Partner and Reseller since 2005

    AspDotNetStorefront Mods and Add-Ons at http://www.ecommercecartmods.com/
    - Searching, Filtering and Sorting (like cSearch, Dealer Locator, Price Ranges, Blog)
    - Reports (like Cart Abandonment and Net Sales)
    - Customer Experience (like Question/Answers)
    - Site and Data Management (like Entity Product Mapper, Bulk Updaters, Make/Model/Year filters)
  3. 05-19-2009, 02:50 PM #3
    sprogg is offline Member
    Join Date
    Jan 2009
    Posts
    79

    Default

    Hi Jan

    I did as suggested, including a full bac-up first.

    The result tells me all row(s) affected but on inspection it seems none were changed.

    Correct me if I'm wrong but to use your code to change the word Hywel Dda to Widgets then I would code:

    UPDATE Product
    SET Description = REPLACE(CONVERT(nvarchar(MAX),Description),'Hywel Dda','Widgets ')

    This is what I did, but no changes were made.

    Help??

    Thanks
  4. 05-19-2009, 02:53 PM #4
    jsimacek is offline Senior Member
    Join Date
    Dec 2008
    Location
    Phoenix, AZ
    Posts
    373

    Default

    That should be fine, did you reload the result set after running it (so you see new data)? Is the spelling correct?
    Jan Simacek - Compunix, LLC
    AspDotNetStorefront trusted Devnet Partner and Reseller since 2005

    AspDotNetStorefront Mods and Add-Ons at http://www.ecommercecartmods.com/
    - Searching, Filtering and Sorting (like cSearch, Dealer Locator, Price Ranges, Blog)
    - Reports (like Cart Abandonment and Net Sales)
    - Customer Experience (like Question/Answers)
    - Site and Data Management (like Entity Product Mapper, Bulk Updaters, Make/Model/Year filters)
  5. 05-19-2009, 03:51 PM #5
    sprogg is offline Member
    Join Date
    Jan 2009
    Posts
    79

    Default

    OK, senior moment now. Can you explain by what reloading the result is please. I'm not sure I've done that.

    Sorry to be so dim.
  6. 05-19-2009, 04:16 PM #6
    jsimacek is offline Senior Member
    Join Date
    Dec 2008
    Location
    Phoenix, AZ
    Posts
    373

    Default

    I'm sorry I wasn't clear...if using the SQL Server Management Studio, re-open the table or rerun the query you had to view your data. If just in cart, reset cache etc if needed. Also, if you need more information see http://msdn.microsoft.com/en-us/library/ms186862.aspx
    Jan Simacek - Compunix, LLC
    AspDotNetStorefront trusted Devnet Partner and Reseller since 2005

    AspDotNetStorefront Mods and Add-Ons at http://www.ecommercecartmods.com/
    - Searching, Filtering and Sorting (like cSearch, Dealer Locator, Price Ranges, Blog)
    - Reports (like Cart Abandonment and Net Sales)
    - Customer Experience (like Question/Answers)
    - Site and Data Management (like Entity Product Mapper, Bulk Updaters, Make/Model/Year filters)
  7. 05-20-2009, 01:31 AM #7
    sprogg is offline Member
    Join Date
    Jan 2009
    Posts
    79

    Smile

    Jan

    Thank you for your help. It did work fine, just me missing the obvious. Guess the lesson is not to work so late and look at things with a fresh pair of eyes!

    Cheers