Post reply

Replacing data in metadata column

  • August 25, 2021 at 7:06 pm

    #3924005

    I need to update/replace category data in the metadata column. When I run my query the category isn't updating with the replacement data. Below is the query I've written, What am I doing wrong? I need to replace Category= with  Category=Vesting Deed

    BEGIN TRANSACTION

    SELECT F.*

    , WalMart_ChainOfTitle.*

    FROM WalMart_ChainOfTitle

    RIGHT OUTER JOIN dbo.tbl_File AS F ON F.ProcessCollectionId = WalMart_ChainOfTitle.ProcessCollectionId

    WHERE F.Deleted = 0

    AND WalMart_ChainOfTitle.ProjectId = 1001

    AND WalMart_ChainOfTitle.CurrentVesting = 1

    AND F.MetaData NOT LIKE '%Category=Vesting Deed%'

    BEGIN TRANSACTION

    UPDATE dbo.tbl_File

    SET dbo.tbl_File.MetaData = ((REPLACE(cast(MetaData AS varchar(max)), 'Category=', 'Category=Vesting Deed')

    FROM WalMart_ChainOfTitle

    RIGHT OUTER JOIN dbo.tbl_File AS F ON F.ProcessCollectionId = WalMart_ChainOfTitle.ProcessCollectionId

    WHERE F.Deleted = 0

    AND WalMart_ChainOfTitle.ProjectId = 1001

    AND WalMart_ChainOfTitle.CurrentVesting = 1

    AND F.MetaData NOT LIKE '%Category=Vesting Deed%'

     

     

  • August 25, 2021 at 9:33 pm

    #3924041

    What is the datatype/size of tbl_File.MetaData? (wondering why are you casting the type in the replace statement)?

    Do you have MetaData column values that contain "Category=" that do not have a value after the equal sign?

    Do you have MetaData column values that contain "Category=" with a value after the equal sign? If so, you will effectively be concatenating the values. I doubt if that is your intention.

    e.g.,

    DECLARE @Metadata VARCHAR(MAX) = 'ID=1,Category=Furniture'
    SELECT @Metadata, REPLACE(@Metadata,'Category=','Category=Vesting Deed')
    WHERE @Metadata NOT LIKE '%Category=Vesting Deed%'

    results in "ID=1,Category=Vesting DeedFurniture"

    If Category values may be populated, and you don't want that concatenated with "Vesting Deed", you should probably provide some sample data.

    • Are there multiple keys (keys other than Category, or multiple instances of Category? If so,

      • Are the keys in the same order? Or could they occur in any order?
      • How are the key/value pairs delimited? Commas? semicolons? Other?

    (If they are in a guaranteed order, and delimiting is consistent, it may be easier to isolate the Category key/value pair and avoid the concatenation bug)

Viewing 2 posts - 1 through 1 (of 1 total)

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