I have a requirement to parse apart a field of data that is separated by a semi-colon, to do some work on the individual results (transform based on a case statement), and then to reassemble so that they are in the format of (value; value; value).
I can separate and perform the case statement just fine. I am using a function i wrote called 'stringsplit' where the input values are the field to be split and the separating value. This works very similar to the new 2016+ SQL function called Split_String, where i cross join to create a table of each value.
I separate the values, perform my CASE statement, but that is where i am stuck.
I would like this to be reusable for multiple different fields, so would prefer a function. But am a bit stuck. currently the SplitString function is being called via a CTE, but i have not been able to find a way to reassemble. I even tried cursors, but i can not call the CTE unless it is a part of the cursor. (this is for one time migration purposes, performance is not critical. Yes, I know the evils of Cursors).
As I must perform this on multiple fields I am really looking for this to be reusable.
My intent was to separate and clean the values via a CTE and then to process the values back together by a function that referenced the CTE values. I am now a bit befuddled.
Any ideas?
My Current CTE is costructed as follows:
The Cursor I was trying to use as a proof of concept is: (my intention was to eventually feed the cursor into a function and utilize dynamic sql to make it reusable.)
I can separate and perform the case statement just fine. I am using a function i wrote called 'stringsplit' where the input values are the field to be split and the separating value. This works very similar to the new 2016+ SQL function called Split_String, where i cross join to create a table of each value.
I separate the values, perform my CASE statement, but that is where i am stuck.
I would like this to be reusable for multiple different fields, so would prefer a function. But am a bit stuck. currently the SplitString function is being called via a CTE, but i have not been able to find a way to reassemble. I even tried cursors, but i can not call the CTE unless it is a part of the cursor. (this is for one time migration purposes, performance is not critical. Yes, I know the evils of Cursors).
As I must perform this on multiple fields I am really looking for this to be reusable.
My intent was to separate and clean the values via a CTE and then to process the values back together by a function that referenced the CTE values. I am now a bit befuddled.
Any ideas?
My Current CTE is costructed as follows:
Code:
WITH ERP_Breakdown AS
(
Select
CASE item
WHEN 'SAP' THEN 'SAP'
WHEN 'Oracle' THEN 'Oracle'
WHEN 'Infor' THEN 'Infor'
WHEN 'PeopleSoft System' THEN 'PeopleSoft'
WHEN 'J D Edwards' THEN 'JD Edwards'
WHEN Null THEN ''
ELSE 'Other' END as ERP,
ID
From Source.dbo.Account
Cross Apply dbo.SplitString(erp__c, ';')
)
Code:
DECLARE @Value as VARCHAR(255)
DECLARE @Result as VARCHAR(255)
DECLARE db_cursor CURSOR FOR
SELECT ERP
FROM ERP_Breakdown
WHERE ID = '0013000000ARZRmAAP'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Value
SET @Result = @Value
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Result = CONCAT(@Result, ';', @Value)
FETCH NEXT FROM db_cursor INTO @Value
END
CLOSE db_cursor
DEALLOCATE db_cursor