comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Re
stlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going
things
type
available
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TE
XT).
> I just need
> to know when the values are different. Are there any functions available t
o
> do this? Please provide any code if you have it or any links regarding thi
s
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdat
a2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Re
stlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going[vbcol=seagreen]
things[vbcol=seagreen]
type[vbcol=seagreen]
available[vbcol=seagreen]
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TE
XT).
> I just need
> to know when the values are different. Are there any functions available t
o
> do this? Please provide any code if you have it or any links regarding thi
s
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdat
a2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...
anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>
|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Restlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =
dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>
|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
arent.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> to
> this
>
|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they[vbcol=seagreen]
> arent.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
going[vbcol=seagreen]
things[vbcol=seagreen]
type[vbcol=seagreen]
available
>
|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TEXT).
> I just need
> to know when the values are different. Are there any functions available to
> do this? Please provide any code if you have it or any links regarding this
> problem.
> Thanks in advance...
>
|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>
|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdata2,1,
Datalength(textdata1))
1

comparing text fields - Second try

Hi all,
What is the fastest and best way to compare two text fields(data type
TEXT). I just need
to know when the values are different. Are there any functions available to
do this? Please provide any code if you have it or any links regarding this
problem.
Thanks in advance...anyone?
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
> to
> do this? Please provide any code if you have it or any links regarding
> this
> problem.
> Thanks in advance...
>|||Yeah, just a moment, coding will take some time, even I am right at home now
;-)
I coded a function to give you the "kind-of" checksum for the two columns.
Due to the fatc that the substring only brings back a varchar from the
function you can normally compare only the first 8000 bytes of a text
string, but... I coded a function which will chop the data into smaller
chunks , produces a checksum of every part and adds up the checksum. I now
that there could be a case that eventually two text columns wil produce the
same checksum, but i think that could be solution you can life with:
CREATE Function CompareText
(
@.EmployeeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.Datalength INT
DECLARE @.Restlength INT
DECLARE @.Checksum INT
DECLARE @.StartChunk INT
DECLARE @.EndChunk INT
SET @.StartChunk = 0
SET @.Datalength = (Select Datalength(Photo) From Employees Where EmployeeID
= @.EmployeeId)
SET @.Restlength = @.Datalength
SET @.Checksum = 0
While @.Restlength > 0
BEGIN
IF @.Restlength > 8000
BEGIN
SET @.EndChunk = 8000
END
ELSE
BEGIN
SET @.EndChunk = @.Restlength
END
SET @.Checksum = @.Checksum + (Select
CHECKSUM(SUBSTRING(Photo,@.StartChunk,@.Restlength)) From Employees Where
EmployeeID = @.EmployeeId)
SET @.StartChunk = @.StartChunk + @.Restlength
SET @.Restlength = @.Restlength - @.EndChunk
END
RETURN @.Checksum
END
This function has to be coded in your way, to not use the Employee Table of
the northwind database.
In this Example you can use the code as following (due to the case there is
only one text/image column in the northwin database:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"helpful sql" <nospam@.stopspam.com> schrieb im Newsbeitrag
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Two text fields in the same record or in related tables? If you are going to
be comparing a large number of records, then you may want to speed things up
by first determing those records where the length of the text values are not
the same. Those are obviosly different. Perhaps store their primary key IDs
in a temporary table. Once done, you can then perform the full test
comparison against the remaining few that are the same size.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type
> TEXT). I just need
> to know when the values are different. Are there any functions available
to
> do this? Please provide any code if you have it or any links regarding
this
> problem.
> Thanks in advance...
>|||Maybe you can use a combination of the answers in your other post and
DATALENGTH ...although this will not catch everything.
If this isn't enough for you, then I don't think you have much choice then
to do the comparison in slices of 8000 characters.
I've never had to do this so I can't help you out much. The SUBSTRING
function can return any slice you want.
Ex: select substring(columnName, 8000, 8000)
But check Datalength first, if that's doesn't match, then you don't have to
go any further.
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uuX8kilVFHA.628@.tk2msftngp13.phx.gbl...
> anyone?
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
>> to
>> do this? Please provide any code if you have it or any links regarding
>> this
>> problem.
>> Thanks in advance...
>>
>|||Some (bad coded) applications write chunks of data in the database in steps
of 50, 100, 200 ... steps, so comparing only the length via Datalength()
could be a problem because many columns would "seem" to be the same but they
aren´t.
Just a experience and my two cents.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> Two text fields in the same record or in related tables? If you are going
> to
> be comparing a large number of records, then you may want to speed things
> up
> by first determing those records where the length of the text values are
> not
> the same. Those are obviosly different. Perhaps store their primary key
> IDs
> in a temporary table. Once done, you can then perform the full test
> comparison against the remaining few that are the same size.
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available
> to
>> do this? Please provide any code if you have it or any links regarding
> this
>> problem.
>> Thanks in advance...
>>
>|||Am I correct in assuming that if the text values have different lengths, for
example 2000 vs. 2100, then they are different without performing a text
comparison? Once we have that list of these candidates in a temporary table,
we can exclude them from the query which performs the text compare.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Some (bad coded) applications write chunks of data in the database in
steps
> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
> could be a problem because many columns would "seem" to be the same but
they
> aren´t.
> Just a experience and my two cents.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
> > Two text fields in the same record or in related tables? If you are
going
> > to
> > be comparing a large number of records, then you may want to speed
things
> > up
> > by first determing those records where the length of the text values are
> > not
> > the same. Those are obviosly different. Perhaps store their primary key
> > IDs
> > in a temporary table. Once done, you can then perform the full test
> > comparison against the remaining few that are the same size.
> >
> > "helpful sql" <nospam@.stopspam.com> wrote in message
> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> >> Hi all,
> >> What is the fastest and best way to compare two text fields(data
type
> >> TEXT). I just need
> >> to know when the values are different. Are there any functions
available
> > to
> >> do this? Please provide any code if you have it or any links regarding
> > this
> >> problem.
> >>
> >> Thanks in advance...
> >>
> >>
> >
> >
>|||Why couldn't you do the following:
Create Table Foo
(
Id Int Primary Key
, TextData1 Text
, TextData2 Text
)
Insert Foo(Id, TextData1, TextData2)...
Select F.*
From Foo As F
Where Substring(F.TextData1,1,DataLength(F.TextData1))
= Substring(F.TextData2,1,DataLength(F.TextData2))
Thomas
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the fastest and best way to compare two text fields(data type TEXT).
> I just need
> to know when the values are different. Are there any functions available to
> do this? Please provide any code if you have it or any links regarding this
> problem.
> Thanks in advance...
>|||NM..Substring returns a max of 8K
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:OYAMCQmVFHA.132@.TK2MSFTNGP14.phx.gbl...
> Why couldn't you do the following:
> Create Table Foo
> (
> Id Int Primary Key
> , TextData1 Text
> , TextData2 Text
> )
> Insert Foo(Id, TextData1, TextData2)...
>
> Select F.*
> From Foo As F
> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
>
> Thomas
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> What is the fastest and best way to compare two text fields(data type
>> TEXT). I just need
>> to know when the values are different. Are there any functions available to
>> do this? Please provide any code if you have it or any links regarding this
>> problem.
>> Thanks in advance...
>>
>|||> Where Substring(F.TextData1,1,DataLength(F.TextData1))
> = Substring(F.TextData2,1,DataLength(F.TextData2))
substring() only returns 8000 chars at a time...
create table FooText(textdata1 text, textdata2 text)
...use the following to build strings > 8000 chars...
--SELECT REPLICATE('a', 8000)
--SELECT REPLICATE('a', 500)
--SELECT REPLICATE('b', 20)
...paste those into an insert statement, then massage the insert statement
so that the last character is different...
INSERT FooText SELECT
'aaa...bbb',
'aaa...bba'
...now watch the result...
select count(*) from FooText
where Substring(textdata1,1, Datalength(textdata1))=Substring(textdata2,1,
Datalength(textdata1))
--
1|||Here's a quick and dirty function that will do the comparison:
Create Function dbo.TextDataAreEqual (@.Text1 Text, @.Text2 Text)
Returns Bit
AS
Begin
Declare @.Len1 Int
Declare @.Len2 Int
Set @.Len1 = DataLength(@.Text1)
Set @.Len2 = DataLength(@.Text2)
If @.Len1 <> @.Len2
Return 0
If @.Len1 <= 8000
If Substring(@.Text1, 1, @.Len1) <> Substring(@.Text2, 1, @.Len2)
Return 0
Declare @.Index Int
Set @.Index = 1
While @.Index < @.Len1
Begin
If Substring(@.Text1, @.Index, 8000) <> Substring(@.Text2, @.Index, 8000)
Return 0
Set @.Index = @.Index + 8000
End
Return 1
End
In essence, it compares on length and then the first 8K and only barring that
does it compare using the chunking methodology that people were talking about in
previous posts. You would get a speed improvment by narrowing the list by
comparing datalength in the main query itself. So something like:
Select *
From (
Select T1.Id, T1.TextField1, T1.TextField2
From dbo.TableName As T1
Where T1.DataLength(T1.TextField1) = T1.DataLength(T1.TextField2)
) As T
Where dbo.TextDataAreEqual(T.TextField1, T.TextField2) = 1
Thomas|||OK that could be done in the query which call´s the function:
Select * from Employees where dbo.Comparetext(EmployeeID) =dbo.Comparetext(EmployeeID)
Where Datalength(EmployeeID) <> Datalength(EmployeeID)
Assuming that u don´t have the same column as shown above, just for the
syntax.
HTH, Jens Suessmeyer.
"JT" <someone@.microsoft.com> schrieb im Newsbeitrag
news:el0N7MmVFHA.1508@.tk2msftngp13.phx.gbl...
> Am I correct in assuming that if the text values have different lengths,
> for
> example 2000 vs. 2100, then they are different without performing a text
> comparison? Once we have that list of these candidates in a temporary
> table,
> we can exclude them from the query which performs the text compare.
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OHuoR3lVFHA.3044@.TK2MSFTNGP10.phx.gbl...
>> Some (bad coded) applications write chunks of data in the database in
> steps
>> of 50, 100, 200 ... steps, so comparing only the length via Datalength()
>> could be a problem because many columns would "seem" to be the same but
> they
>> aren´t.
>> Just a experience and my two cents.
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "JT" <someone@.microsoft.com> schrieb im Newsbeitrag
>> news:%23MeyfzlVFHA.228@.TK2MSFTNGP12.phx.gbl...
>> > Two text fields in the same record or in related tables? If you are
> going
>> > to
>> > be comparing a large number of records, then you may want to speed
> things
>> > up
>> > by first determing those records where the length of the text values
>> > are
>> > not
>> > the same. Those are obviosly different. Perhaps store their primary key
>> > IDs
>> > in a temporary table. Once done, you can then perform the full test
>> > comparison against the remaining few that are the same size.
>> >
>> > "helpful sql" <nospam@.stopspam.com> wrote in message
>> > news:eQ2M2AlVFHA.132@.TK2MSFTNGP14.phx.gbl...
>> >> Hi all,
>> >> What is the fastest and best way to compare two text fields(data
> type
>> >> TEXT). I just need
>> >> to know when the values are different. Are there any functions
> available
>> > to
>> >> do this? Please provide any code if you have it or any links regarding
>> > this
>> >> problem.
>> >>
>> >> Thanks in advance...
>> >>
>> >>
>> >
>> >
>>
>

compare two tables

Hi all,
We have one master table with all records of the second table but the second
table doesn't contains all the records of the master table.
Like:
Master Table
ID Value
1 A
1 B
1 C
1 D
2 A
2 B
Second Table
ID Value
1 A
1 D
2 A
How can i compare so I can have this result for 2 million records?
1 B
1 C
2 B
Tks in advance
JFBSELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
FROM MasterTable
LEFT OUTER JOIN SecondTable
ON MasterTable.Id = SecondTable.Id
AND MasterTable.Value = SecondTable.Value
WHERE SecondTable.ID IS NULL
Should display all the records from the Master Table that do not have a
match in the SecondTable.
"JFB" <help@.jfb.com> wrote in message
news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> We have one master table with all records of the second table but the
> second table doesn't contains all the records of the master table.
> Like:
> Master Table
> ID Value
> 1 A
> 1 B
> 1 C
> 1 D
> 2 A
> 2 B
> Second Table
> ID Value
> 1 A
> 1 D
> 2 A
> How can i compare so I can have this result for 2 million records?
> 1 B
> 1 C
> 2 B
> Tks in advance
> JFB
>|||Great Bill... tks for you help :)
Rgds
JFB
"Bill Edwards" <billedwards@.msn.com> wrote in message
news:u8sxPT56FHA.3648@.tk2msftngp13.phx.gbl...
> SELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
> FROM MasterTable
> LEFT OUTER JOIN SecondTable
> ON MasterTable.Id = SecondTable.Id
> AND MasterTable.Value = SecondTable.Value
> WHERE SecondTable.ID IS NULL
> Should display all the records from the Master Table that do not have a
> match in the SecondTable.
> "JFB" <help@.jfb.com> wrote in message
> news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
>sqlsql

Compare two databases and update objects?

Is there a way to compare two databases one being an old
database and the second being a new database, I will
update the old database's objects to match the new database's
objects?

Is there SQL code that could do this easily?

How do you do this if you deal with the same scenario?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> Is there a way to compare two databases one being an old
> database and the second being a new database, I will
> update the old database's objects to match the new database's
> objects?
> Is there SQL code that could do this easily?

The standard recommendation is to look at SQL Compare from Red Gate.

> How do you do this if you deal with the same scenario?

I'm avoiding it by having my code under version control, and keeping
track of what I shipped. Our load tool actuall has its own set of tables
to do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||DB Ghost is a tool that will compare databases and upgrade a target
database to make it match a source.

It also integrates with any source control system so the 'source'
database becomes a simple set of drop/create scripts. The power of
this approach is that it enables any size of development team to work
on the schema under source control at the same time using the same
mechanism that they do for other application code such as C#, Java, VB,
C++ etc. This provides you with a full audit trail of who made what
changes to the schema, when and why.

This process is one that delivers more as your development needs
increase. For example it doesn't matter how many developers work on
the scripts in source control, the amount of time required to extract
all the scripts and perform the upgrade doesn't noticeably increase.
Also, this process works perfectly with parallel development as it
means that concepts such as isolated worksets and merges between code
lines becomes as easy as it is with normal application code. Try doing
parallel development with delta scripts, it is a completely error prone
manual process of looking through the delta scripts to work out what
has been changed!

Diff tools such as SQL Compare are great at what they do but,
ultimately, they are really just there to get you out of trouble.

This trouble is normally caused by not having proper processes and
tools to control changes to your schema.

DB Ghost plus any source control system gives you such a process.

Malcolm
www.dbghost.com
Build, Compare and Synchronize = Database Change Management for SQL
Server

Compare database on developer and enduser server

Hello Group,
Sorry my english is very littel.
In my company I have two servers: first developer server (DevSvr)- for
developers and second server (MainSvr)to use for other emplooy - for user of
application.
One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
How compare databeses on DevSvr and MainSvr (db structure) and create script
to alter database on MainSvr.
Thx PawelRtry www.red-gate.com
Lutz|||Before you invest several hundred dollars in a schema comparison tool,
consider the option of using Enterprise Manager to generate scripts for each
DB to seperate folders and using a GPL tool like WinMerge to perform the
file comparisons. You can configure to list only scripts and individual
lines that are different.
http://groups.google.com/group/micr...f9cfe0c46abfa76
Also, if you are checking your scripts into Visual Source Safe, there is a
feature to perform file comparisons between projects.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:ecxsuFjqFHA.3800@.TK2MSFTNGP11.phx.gbl...
> Hello Group,
> Sorry my english is very littel.
> In my company I have two servers: first developer server (DevSvr)- for
> developers and second server (MainSvr)to use for other emplooy - for user
> of application.
> One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
> How compare databeses on DevSvr and MainSvr (db structure) and create
> script to alter database on MainSvr.
> Thx PawelR
>|||You can use my free tools SQLDBDiff, the first time you lunch the tool it is
in French you can easly switch to English version by clicking :
Taches/Options/Language
Here is the link (My site will be in English soon)
http://www.sqldbtools.com/Downloads.aspx?ProductId=1
I'm waiting for your comments and feedback
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:ecxsuFjqFHA.3800@.TK2MSFTNGP11.phx.gbl...
> Hello Group,
> Sorry my english is very littel.
> In my company I have two servers: first developer server (DevSvr)- for
> developers and second server (MainSvr)to use for other emplooy - for user
> of application.
> One of databases was changed on DevSvr (add new tables, sp, indexes etc.).
> How compare databeses on DevSvr and MainSvr (db structure) and create
> script to alter database on MainSvr.
> Thx PawelR
>

Compare BIGINT ranges to check for overlapping

I have a table that has two BIGINT columns. The first column is for storing the beginning of a range and the second column is for storing the end of the range.

I'm trying to find a way to check to see if the BIGINT values of an INSERT statement are present within existing BIGINT ranges. If so, I want the insert statement to fail.

A simplified example of this would be this:

EXISTING DATA
col1 col2
100000 110000

NEW DATA
col1 col2
101000 101999

I don't want the new data to be inserted b/c at least one of the values contained in its range is already present in the existing data.

I've seen ways to do this with date ranges, but haven't figured out how to accomplish this with BIGINTs.

Any help would be appreciated.

Thanks,

AndrewIf you have something (a constraint or trigger) in place to make sure that col1 < col2, then you can easily check to see if there is an existing row for which both existing.col1 and new.col1 are less than both existing.col2 and new.col2

If you picture this, you will see why. If one of the col2s shuts off a range before the other col1 starts, then you don't have an overlap.

Now, since we already know that:

existing.col1 <= existing.col2
and
new.col1 <= new.col2

All we need to do to check is:

where existing.col1 <= new.col2
and new.col1 <= existing.col2

So, you want something like a trigger which does:

if exists (
select *
from inserted i
where exists (select * from myTable e where e.col1 <= i.col2 and i.col1 <= e.col2)
)
rollback transaction

Now, you will want to make sure you have a trigger on myTable which covers both col1 and col2, because you want this to run quickly. But it should work. :)

Rob|||And by the way - <= and >= will work just fine with bigints... :)|||Rob,

Thank you very much for your answer. Problem solved!

Andrew