String compare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String compare

Author

Topic

SteevR
Starting Member

18 Posts

Posted - 2011-07-06 : 16:22:27

Hello. I have an audit table that tracks user changes of a large number of checkboxes. I save each of the IDs in a comma separated string in the audit table. Here is a basic dataset:

oldValue   newValue
NULL     1,2,3,4
1,2,3,4     1,3,5
1,3,5     2,3,4

What I would like is to be able to run a stored procedure that just shows me the difference between the two. Like so:

oldValue    newValue    added    removed
NULL    1,2,3,4        1,2,3,4    NULL
1,2,3,4    1,3,5            5    2,4
1,3,5    2,3,4          2,4        1,5

Is this possible?

TIA,
Steve
PS sorry for the crappy layout...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 04:24:47

Yes but you would need to split up the string into it's constituent parts for the compare.
It would probably be better to change the way you log - either add an id and long a val;ue per row or a bit map.
For the second row
id, type val
2 o 1
2 o 2
2 o 3
2 o 4
2 n 1
2 n 3
2 n 5

or
15 21

then it becomes easy

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SteevR
Starting Member

18 Posts

Posted - 2011-07-07 : 08:40:55

Hi Nigel,

Thanks for the response. Unfortunately, I cannot change the way the data is logged. It is a trigger that grabs all the data for changed fields when the user adds, updates or deletes a record from another table. The checkbox data all comes in as CSV as shown. Bascially its just laziness on my part; I just want to see the changes made without comparing myself.

Oh, I also forgot to mention there is an ID already; I just left it out. Sorry. That was just an example of data with the same ID.

Any other suggestions?

TIA,
Steve

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 08:42:19

I would say the lazy thing to do would be to change the trigger.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 08:56:37

Something like this for the additions.
Turn it into a full outer join to get changes and add another string. Join back to cte to get the old and new values.
I haven't run this just types it in so there are probably issues but it should be close.

with cte as (select *, seq = row_number() over (order by oldvalue))
, cteold as
(
select seq, loc=1, loc2=charindex(',',oldValue), oldvalue from tbl
union all
select seq, loc=loc2+1, loc2=charindex(',',oldValue,loc2+1), oldvalue from cteold where loc2 <> 0
)
, ctenew as
(
select seq, loc=1, loc2=charindex(',',newValue), newvalue from tbl
union all
select seq, loc=loc2+1, loc2=charindex(',',newValue,loc2+1), newvalue from ctenew where loc2 <> 0
)
,cteold1 as
(
select seq, oldvalue, substring(oldvalue, loc, case when loc2 = 0 then len(oldvalue)+1 else loc2 end - loc + 1)
from cteold
)
,ctenew1 as
(
select seq, newvalue, val=substring(newvalue, loc, case when loc2 = 0 then len(newvalue)+1 else loc2 end - loc + 1)
from ctenew
)
,ctediff as
(
select *
from ctenew2
left join cteold2
on ctenew2.seq = cteold2.seq
and ctenew2.val = cteold2.val
where cteold2.seq is null
)
select seq, newvalue
diff = stuff (
(
select ',' + val
from @table t2 where t.seq = t2.seq
for xml path('')
)
,1,1,'')
from (select distinct seq, newvalue from ctediff) t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SteevR
Starting Member

18 Posts

Posted - 2011-07-07 : 10:53:44

Hi Nigel,

I am not being too lazy to change the trigger; it is being used in a number of tables to grab changed data and recording it in the audit table. In this particular instance, the user selected checkbox IDs are saved in CSV format in its table and saved to the audit table via the trigger. When I want to track what particular user has changed what checkboxes is where I am being lazy. Instead of spewing out oldValues and newValues and manually comparing from the audit table, I was looking for a different approach.

Thanks,
Steve

SteevR
Starting Member

18 Posts

Posted - 2011-07-08 : 11:52:33

Hi Nigel,

I was able to get it by creating a function that creates two temporary tables, populates them with the CSV data and simply run a select statement that grabs the differences using SELECT NOT IN.

Then from my SQL I can then:

SELECT oldValue,newValue,(SELECT dbo.udf_ReturnDifferences(AuditTable.NewValue,AuditTable.OldValue)) as added,(SELECT dbo.udf_ReturnDifferences(AuditTable.OldValue,AuditTable.NewValue)) as removed FROM... etc

Worked like a charm.

Thanks for your help,
Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources