ingnore white space while searching numbers

select stripcharacter(Telephone) from yourdatabase

  
CREATE DEFINER = 'YOURUSER'@'%' FUNCTION `StripCharacter`(vSTR CHAR(50))  
    RETURNS char(50) CHARSET latin1  
    NOT DETERMINISTIC  
    CONTAINS SQL  
    SQL SECURITY DEFINER  
    COMMENT ''  
BEGIN  
declare X1,X2 INT DEFAULT 0;  
declare vSTRreturn char(50) DEFAULT '';  
  
  
WHILE x1 < LENGTH(vSTR) DO  
   set x1=x1+1;  
   if SUBSTRING(vSTR,X1,1)>= '0' and SUBSTRING(vSTR,X1,1)<= '9' THEN  
      set vSTRReturn = concat(vSTRReturn,SUBSTRING(vSTR,X1,1));  
   End If;  
End WHILE;  
  
  RETURN vSTRReturn;  
END;  

  
CREATE DEFINER = 'YOURUSER'@'%' FUNCTION `StripCharacterX`(vSTR CHAR(50))    
    RETURNS char(50) CHARSET latin1    
    NOT DETERMINISTIC    
    CONTAINS SQL    
    SQL SECURITY DEFINER    
    COMMENT ''    
BEGIN    
declare X1 INT;    
declare X2 INT;  
declare vSTRreturn char(50);    
SET X1=0;  
SET X2=0;  
SET vSTRreturn = '';  
    
    
WHILE x1 < LENGTH(vSTR) DO    
   set x1=x1+1;    
   if SUBSTRING(vSTR,X1,1)>= '0' and SUBSTRING(vSTR,X1,1)<= '9' THEN    
      set vSTRReturn = concat(vSTRReturn,SUBSTRING(vSTR,X1,1));    
   End If;    
End WHILE;    
    
  RETURN vSTRReturn;    
END;    

Quote datadoit:

Turns out to be a Navicat issue.

Working for me now! Ripping through about 50k records for a phone
number returned three records in about 8 seconds or so. Not too bad.

Thanks for sharing!

Quote datadoit:

Oper wrote:
> odd should not be that slow on 5k records we did the same for a 100k records
> and
> was like 5-8sec at that time. (maybe server configuration)
>

You're right. It is only a few seconds. Forgot I was running through a
remote tunnel before, hence the latency. Do you think with or without
an index on the searched field helps/hinders?