Replacing data in metadata column

• mstamara.morgan

  SSC Veteran

  Points: 272

  More actions

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

   

   

• ratbak

  Default port

  Points: 1416

  More actions

  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)