Hi Jeff
So what you are looking at there is the internal format of the variant and options which have been selected for the product that the customer has ordered.
I'm not a fan of doing too much logic and coding in SQL server, its a db query language and doesn't have enough programming features for me.
Having said that there is a much easier way to do what you want. In Cart Viper you can define a class which gets executed when an item is purchased. I would use this feature to write a custom assembly which contains the C# code to extract the data in the variant attribute for the order details and then push this into the custom table you have.
You can use regex for example to work out the field boundaries and determine if the item is a new order or a renewal.
To make write the code you need to create a class which implements this interface
CartViper.Modules.Store.Cart.PaymentCompleted.IPaymentCompletedHandler which is in the assembly CartViper.Modules.Store.dll
Once you have the dll created you then need to register the setting in a table in the database.
Please add your own two part type and assembly name to this statement to register the class. ( 'myclass.handler.ordercomplete.classname,assemblyname', these need to be changed to the correct values for your class and assm name)
Once you have executed the SQL statement you then need to clear the cache in DNN.
declare @portalId int
set @portalId = 0
if not exists(select null from {databaseOwner}[{objectQualifier}CVStore_StoreAttributes] where parentId = @portalId and name = 'CV_STORE_PAYMENT_COMPLETED_TYPE')
begin
insert into {databaseOwner}[{objectQualifier}CVStore_StoreAttributes]
(parentId,name,value) values(@portalId, 'CV_STORE_PAYMENT_COMPLETED_TYPE', 'myclass.handler.ordercomplete.classname,assemblyname')
end
We would be happy to quote for creating a solution around this idea if you would prefer us to do it. Please email me mark@cartviper.com
Regards
Mark