Find Duplicates within a string

[colin]

Hi,

I have a contacts database and need to keep it data cleansed to avoid
duplicate records.

I propose to concatonate all fields into one and would then like to run a
series of queries to identify whether a string of a given length for one
contact matches that of another contact then display the uniqueID of the
records in a crosstab for example:

UniqueID Concatonated
1 dsadaqwerty1
2 qwerty1
3 test
4 djjdfjd
5 1test2121
6 sddsasdasd

dsadaqwerty1 qwerty1 1 2
test 1test2121 3 5

I would need to be able to set the length of the string that is being
checked ie if it was set at 5 then only the results containg qwerty would
appear.

The reason for concatonation is that telephone numbers can be entered in
error into the incorrect fields eg landline into a mobile etc.

Many thanks

Colin

 

[KARL DEWEY]

I think you will need another query and temp table.
Use a Ranking in a Group query to add Rank number for all of same lenght.
UniqueID Concatonated Lenght Rank
1 dsadaqwerty1 12 1
2 qwerty1dsada 12 2
3 test12121 9 1
5 1test2121 9 2
4 djjdfjd 7 1
6 jddjjdf 7 2

Then out of temp table the crosstab would look like this --
Rank ..........
Record Text
Lenght 1 2
7 4 djjdfjd 6 jddjjdf
9 3 test12121 5 1test2121
12 1 dsadaqwerty1 2 qwerty1dsada

 

[colin]

Hi Karl,

Many thanks for the reply - Just to clarify I would like to match a part of
a string which I want to limit in length eg

Qwerty has the length 5
zxcvbnm has the length 7

data string concatonated can be any length

skjfskdfhlkfjghdkfghdlkfghQWERTYkdflgks;glfkgjs;dlfkjg Uniqueid 1
sldkfjssdklfQWERTYsdfkljls
Uniqueid 2
sewshjhfgsjfldfZXCVBNMsdhfsdfhfsjfh Uniqueid 3
sZXCVBNMfsdggfdjgh
Uniqueid 4

So if I want to find matches where the instring length is set to 7 then
unique matches would be unique id 3 and 4 displaying the string in the format
below.

Any Suggestions appreciated.

Thanks

Colin

 

[KARL DEWEY]

I got lost. Record 3 and 4 are not duplicates. Are you trying to match the
contents of field with concatenated fields or concatenated field to match
concatenated field?

If you want to find a string within a concatenated string then the lenght is
of no consequence.