0

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

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.

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?

Improve this question
1
  • 1
    Why don't you TRIM all spaces before comparing? Commented Oct 3, 2013 at 10:03

3 Answers 3

Reset to default
1

You can use REPLACE in select query also:

select REPLACE( '07711 123 456', ' ', '');
+------------------------------------+
| REPLACE( '07711 123 456', ' ', '') |
+------------------------------------+
| 07711123456                        |
+------------------------------------+
1 row in set (0.06 sec)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

...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.

Improve this answer

Comments

0

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 `table_one`.fn_ShowOnlyNumbers(mobile) != `table_two`.fn_ShowOnlyNumbers(mobile)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.