January 6, 2004 at 10:04 am
How can I compare values in two text fields? Any help is appreciated. Thanks -GJK
January 6, 2004 at 10:48 am
LIKE
create table MyTest1
( myid int
, mttxt text
)
go
create table MyTest2
( myid int
, mttxt text
)
go
insert mytest1 select 1, 'This is a test'
insert mytest1 select 2, 'This is a test2'
insert mytest2 select 1, 'This is a tester'
insert mytest2 select 3, 'This is a test2'
go
select *
from mytest1 a
inner join mytest2 b
on a.mttxt like b.mttxt
go
drop table Mytest1
drop table Mytest2
January 6, 2004 at 12:40 pm
Keep in mind that there is the possibility of incorrect results if the field following 'LIKE' includes wildcard characters. The inclusion of '_' or '%' might cause two unlike text fields to match, and the existence of characters surrounded by square brackets could cause false negatives. For example:
declare @tmp table (field text)
declare @text table (field text)
INSERT INTO @tmp VALUES ('My first text field')
INSERT INTO @tmp VALUES ('My second text field')
INSERT INTO @tmp VALUES ('My third text field')
INSERT INTO @tmp VALUES ('I plan on giving 100% to this effort')
INSERT INTO @tmp VALUES ('We used the variable name ''myLname''')
insert into @tmp VALUES ('A different text field [and what happens with a parenthetical using square brackets?]')
--False positive on comparison
insert into @text VALUES ('My _____ text field')
select * from @tmp a INNER JOIN @text b ON (a.field LIKE b.field)
DELETE @text
--Another false positive on _
insert into @text VALUES ('We used the variable name ''my_name''')
select * from @tmp a INNER JOIN @text b ON (a.field LIKE b.field)
DELETE @text
--False positive w/ %
insert into @text VALUES ('My %')
select * from @tmp a INNER JOIN @text b ON (a.field LIKE b.field)
DELETE @text
--Another false positive w/ %
insert into @text VALUES ('I plan on giving 10% to this effort')
select * from @tmp a INNER JOIN @text b ON (a.field LIKE b.field)
DELETE @text
--False negative due to use of square brackets
insert into @text VALUES ('A different text field [and what happens with a parenthetical using square brackets?]')
select * from @tmp a INNER JOIN @text b ON (a.field LIKE b.field)
DELETE @text
Admittedly, I've created some contrived examples; but the point is well-taken. If you are confident your data is safe from this sort of problem, this solution will work fine. And I don't have any efficient solution that would work better (a quick to imagine, yet inefficient solution is to check the DATALENGTH of both text fields, and to then use SUBSTRING to compare them character by character (Hey, I said it was inefficient!)).
Good luck!
R David Francis
January 6, 2004 at 1:37 pm
WHERE ISNULL(SUBSTRING(a.TexrCol,DATALENGTH(a.TextCol)),'~~Null~~') = ISNULL(SUBSTRING(b.TextCol,DATALENGTH(b.TextCol)),'~~Null~~')
--Jonathan
January 7, 2004 at 8:44 am
Thanks for all the replies. What if we want to detect any changes to value in a text field. For eg: I have a table called EMPLOYEE and a Comment field of type text. If somebody changes the comment field, I would like to store the previous value and new value in Log table. I tried to use the Inserted and Deleted tables in the trigger, but Text columns are not available in the trigger. Is it possible to do this.
Thanks
-GJK
January 7, 2004 at 9:49 am
you could allow changes to the data only through SP and Hadle that IN the SPs !!!
HTH
* Noel
January 7, 2004 at 9:57 am
That's still an application-level solution - users with the right permission could still make changes directly through SQL. Even if our quester is the only person with such access, it's nice to handle these things automatically.
I'm not sure this would work, but here's a thought, at least.
IF you can get at the current (updated) value of the text field by accessing the table directly (rather than inserted or deleted),
and IF you an find a tool that can give you a checksum on the text,
THEN you could store the checksum, and compare the one in deleted to what the trigger gets - if it's different, the text field changed, and you need to update the checksum in the record and do you change processing.
Have no idea if this is really possible, but if no one proposed slightly outlandish ideas ... then something bad will happen. I think.
R David Francis
January 7, 2004 at 9:59 am
Re-read the part about storing the previous and new values in the log table. If what I said would work, then you'd just store the updated value in the log - but you should have the old value in the log somewhere already. If you log to a database table, you might even be able to point to the old one.
R David Francis
January 7, 2004 at 10:12 am
You could use an INSTEAD OF trigger and check the text column(s) using IF UPDATE() and then joining the table to deleted on the primary key and sending the text value to the log table. Then just do the update and other logging operations in the subsequent trigger code.
If you use WRITETEXT to overwrite text values (which can be a good idea, along with the bulk-logged recovery model) this will not work, as the trigger will not be fired.
--Jonathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply