Compare Text Columns - 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 2000 Forums

Transact-SQL (2000)

Compare Text Columns

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-08-21 : 12:04:16

I have some data in a staging table, and I want to update a table (with matching PK column) if there is a difference in a TEXT column

What's my best way?

I'm thinking of CheckSum + data_length(), but that's not going to be a 100% safe solution is it?

(SQL 2000)

Thanks,

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 12:14:48

Levenstheins distance algorithm? Not very fast but accurate.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 12:23:24

LIKE operator works in text columns.

CREATE TABLE	t1
		(
			id int IDENTITY (1, 1),
			A text
		)

insert		t1
		(
			a
		)
select		'peter' union all
select		'jennie' union all
select		'jenny'

select		w.*,
		y.*,
		case when w.a like y.a then 1 else 0 end
from		(
			select	*
			from	t1
		) w
cross join	(
			select	*
			from	t1
		) y

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-08-21 : 13:08:48

Hadn't thought of LIKE, but a TEXT ending in "%" is going to make a bit of a mess of it!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 13:27:05

LIKE and DATALENGTH, both?

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-21 : 13:32:01

If you know the maximum possible length of the text data then you can substring into 8000 character strings and compare them.


where
	substring(MyText,    1,8000) = substring(MyText1,    1,8000) and
	substring(MyText, 8001,8000) = substring(MyText1, 8001,8000) and
	substring(MyText,16001,8000) = substring(MyText1,16001,8000) and
	... and so on ...

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-08-21 : 14:44:34

Thansk MVJ, that's what I have gone with

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 14:58:16

Now, when we got a chance, is there a speed difference between the two suggestions?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources