double entries

[windandwaves]

Hi Folk

I have a databse with a bunch of double entries in some fields. For
example:
field A:

"that is the way to go that is the way to go "

field A should be:

"that is the way to go "

Is there some sort of sql I can use to find these (and clean them up)?

TIA

Nicolaas

[strawberry]

On Mar 24, 8:34 pm, "windandwaves" <[e-mail]:

Hi Folk

I have a databse with a bunch of double entries in some fields. For
example:

field A:

"that is the way to go that is the way to go "

field A should be:

"that is the way to go "

Is there some sort of sql I can use to find these (and clean them up)?

TIA

Nicolaas

Is there some sort of sql I can use to find these

Yep. Assuming they really are exact doubles (phrase,space,phrase)
here's one way:

SELECT TRIM( SUBSTR(
fieldA, 1, FLOOR( LENGTH( fieldA ) /2 ) )
)
START , TRIM( SUBSTR(
fieldA, CEILING( LENGTH( fieldA ) /2 ) )
)finish
FROM mytable
HAVING START = finish
LIMIT 0 , 30

>(and clean them up)?

I'll leave that as an execise for the reader. WARNING: BACK UP YOUR
DATA BEFORE YOU ATTEMPT THIS!!!