CODEWITHSUNDEEP
mysql
VengaVenga
VengaVenga

Reputation: 760

Simple MySQL CONCAT function

This function should skip digits from 0 to 9 - nothing else. Why does it also skip blanks? E.g. strip_digits("St. Johnes") results in "St.Johnes"

BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(255) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str );
  SET ret = '';
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF ASCII(c)<ASCII('0') OR ASCII(c)>ASCII('9') THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END

Upvotes: 0

Views: 84

Answers (1)

an33sh
an33sh

Reputation: 1124

Its because you are using CHAR instead of VARCHAR. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

You can read it here

change both 'ret' and 'c' to VARCHAR.

Upvotes: 1

Related Questions