MySQL查询，删除所有空格 [英] MySQL Query, remove all spaces

问题描述

我有一个不寻常的查询，这使我现在陷入困境

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

表字段为:

id    bigint  20
name  varchar 255
desc  text

很多记录具有相同的名称和desc，但是desc在单词之间有一些额外的空格

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

喜欢

1   't1'   'hello world'
2   't2'   'hello                world'

我需要找到那些数据相似的行

I need to find those rows that have similar data

如何找到这些，谢谢.

推荐答案

这非常接近.假设:

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

| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| d     | text    | YES  |     | NULL    |       |
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

然后查询:

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);

为您获取重复的行.如果您有"Helloworld"(即没有空格)并且不希望与之匹配，则会失败.

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

这篇关于MySQL查询，删除所有空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！