mysql 去空格,MySQL查询，删除所有空格

I have an unusual query, which got me stuck right now

Table Fields are:

id bigint 20

name varchar 255

desc text

There are many records with same name and desc, but desc have some extra spaces in between words

like

1 't1' 'hello world'

2 't2' 'hello world'

I need to find those rows that have similar data

How can I find these, thanks.

解决方案

This is pretty close. Assuming:

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| d | text | YES | | NULL | |

| id | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

Then this query:

select x.id,x2.id,x.d,x2.d from x left join x as x2 on replace(x.d," ","") = replace(x2.d," ","") and x.id != x2.id having !(x2.id is null);

Gets you the duplicate rows. It fails if you have "Helloworld" (i.e. with no space) and you don't want that to match.