May 29, 2017 at 5:51 pm
I have a history table that stores column changes in a column in a string format(i.e. |LastName:Smith|Jones|FirstName:Bill|William). I have a requirement to de-identify such data and I'm trying to figure out the best way to do this. I'm thinking if I could parse out this data to a table that would contain the ColumnKey, the name of the column, and the value, I could then de-identify the data and then put it back in the string format. Obviously, this is a very bad table design to store the column name, the old value , the new value(i.e. ColumnName:OldValue|NewValue), but, I'm stuck with it right now.
Thanks in advance.
May 29, 2017 at 11:27 pm
I got the chunks apart... So I guess it's a place to start. You need Jeff's string splitter function for this, which is here. And here's my code... You would do this against a table with a select query, but you didn't give me a table, so I cheated some.
DECLARE @ChangeString VARCHAR(100) = '|LastName:Smith|Jones|FirstName:Bill|William';
SET @ChangeString = RIGHT(@ChangeString,LEN(@ChangeString)-1);
SELECT x.ItemNumber
, x.Item
FROM Scratchpad.dbo.DelimitedSplit8K(@ChangeString, '|') x;
INSERT INTO #TempNames(ItemNumber, Item)
(SELECT x.ItemNumber
, x.Item
FROM Scratchpad.dbo.DelimitedSplit8K(@ChangeString,'|') x );
SELECT RecID
, ItemNumber
, Item
, RIGHT(Item,LEN(Item) - CHARINDEX(':', Item)) AS NamePart
, CASE WHEN ItemNumber%2=1 THEN 'Old Value' ELSE 'New Value' END WhichVal
FROM #TempNames;
May 30, 2017 at 2:01 am
You may be able to use the PARSENAME function but that depends on the data.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @ChangeString VARCHAR(128) = '|LastName:Smith|Jones|FirstName:Bill|William';
;WITH REMOVE_LEADING_TOKEN(OUT_STR) AS
(
SELECT REPLACE(SUBSTRING(@ChangeString,2,128),'|','.') AS OUT_STR
)
SELECT
PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,4),':','.'),2) AS COL_01
,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,4),':','.'),1) AS COL_02
,PARSENAME(RLT.OUT_STR,3) AS COL_03
,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,2),':','.'),2) AS COL_04
,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,2),':','.'),1) AS COL_05
,PARSENAME(RLT.OUT_STR,1) AS COL_06
FROM REMOVE_LEADING_TOKEN RLT;
Output
COL_01 COL_02 COL_03 COL_04 COL_05 COL_06
--------- ------- ------ ---------- ------- --------
LastName Smith Jones FirstName Bill William
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply