MySQL - 在查询中替换列中的空格

In a query I'm comparing mobile numbers against another (mobile) value in another column.

在查询中,我将移动数字与另一列中的另一个(移动)值进行比较。

So far I'm doing this by something like ...WHERE `table_one`.`mobile` != `table_two`.`mobile

到目前为止,我正在做的事情是...... WHERE`table_one``mobile`!=`table_two``mobile

The problem is there are identical mobile numbers but separated by different spaces in each of the column ie 07711 123 456 and 07711 123456 therefore returning results when there is a match.

问题是存在相同的移动号码,但是每列中的不同空格分开,即07711 123 456和07711 123456,因此在匹配时返回结果。

I know how to do this during an update, something like update `table` set number = REPLACE( number, ' ', ''); but I can't see any examples where this is done in the middle of a query. Perhaps doing a check, placing into a MySQL variable, doing the same with the other mobile in the additional column then comparing the two variables?

我知道如何在更新期间执行此操作,例如更新`table` set number = REPLACE(number,'','');但我看不到任何在查询过程中完成此操作的示例。也许做一个检查,放入一个MySQL变量,在附加列中对其他移动设备做同样的事情,然后比较两个变量?

3 个解决方案

#1

You can use REPLACE in select query also:

您也可以在select查询中使用REPLACE:

select REPLACE( '07711 123 456', ' ', '');
+------------------------------------+
| REPLACE( '07711 123 456', ' ', '') |
+------------------------------------+
| 07711123456                        |
+------------------------------------+
1 row in set (0.06 sec)

#2

@Nadeem_MK, thanks for that. I managed to write the query I needed without too much effort.

@Nadeem_MK,谢谢你。我设法编写了我需要的查询而不需要太多努力。

...WHERE (REPLACE(`tbl_one`.`mobile`, ' ', '')) != (REPLACE(`tbl_two`.`mobile`, ' ', ''))

... WHERE(REPLACE(`tbl_one` .mobile`,'',''))!=(REPLACE(`tbl_two` .mobile`,'',''))

I did try the TRIM function but I could only manage to clear leading or trailing spaces and with two select statements the query was longer than when I used the REPLACE function.

我确实尝试过TRIM函数,但我只能设法清除前导空格或尾随空格,并且使用两个select语句,查询比我使用REPLACE函数时更长。

#3

You can create a function that uses regexp to only allows numbers. This way it will remove + - ( ) or any possible characters that is not a integer

您可以创建一个使用正则表达式只允许数字的函数。这样它将删除+ - ()或任何不是整数的可能字符

DELIMITER $$

CREATE FUNCTION `fn_ShowOnlyNumbers`(str VARCHAR(1000)) RETURNS varchar(1000) CHARSET latin1
BEGIN
  DECLARE counter INT DEFAULT 0;
  DECLARE strLength INT DEFAULT 0;
  DECLARE strChar VARCHAR(1000) DEFAULT '' ;
  DECLARE retVal VARCHAR(1000) DEFAULT '';

  SET strLength = LENGTH(str);

  WHILE strLength > 0 DO
    SET counter = counter+1;
    SET strChar = SUBSTRING(str,counter,1);
    IF strChar REGEXP('[0-9]') = 1
      THEN SET retVal = CONCAT(retVal,strChar);
    END IF;
    SET strLength = strLength -1;
    SET strChar = NULL;
  END WHILE;
RETURN retVal;
END

Then you can simply use the function in your where clause.

然后,您只需在where子句中使用该函数即可。

...WHERE `table_one`.fn_ShowOnlyNumbers(mobile) != `table_two`.fn_ShowOnlyNumbers(mobile)

#1