Mysql - number of letters

Question

Can someone tell me how to read letters in a string in MySQL?

For instance:

SELECT numberOfLetters('abc123 def') 

will return 6

By the letter I mean AZ and az.

numberOfLetters is not valid SQL, of course, but it illustrates what I'm trying to do.

The version of MySQL that I am using is 5.5.27

Answer 1

You will need a function:

 DROP FUNCTION IF EXISTS numberOfLetters; DELIMITER // CREATE FUNCTION numberOfLetters(s VARCHAR(255)) RETURNS INT DETERMINISTIC NO SQL BEGIN DECLARE c INT; DECLARE r INT DEFAULT 0; DECLARE n INT DEFAULT LENGTH(s); DECLARE i INT DEFAULT 1; WHILE i <= n DO SET c = ASCII(SUBSTRING(s, i, 1)); IF (c >= 65 AND c <= 90) OR (c >= 97 AND c <= 122) THEN SET r = r + 1; END IF; SET i = i + 1; END WHILE; RETURN r; END// DELIMITER ; 

And then call:

 SELECT numberOfLetters('abc123 def'); 

Answer 2

Pretty bad, but I think this will do what you need.

NOTE. Assuming ASCII and you count only az, AZ.

 DROP FUNCTION IF EXISTS numberOfLetters; DELIMITER // CREATE FUNCTION numberOfLetters (inStr CHAR(255)) RETURNS INT BEGIN DECLARE strLen INT; DECLARE letterCt INT; DECLARE pos INT; DECLARE curLetter CHAR(1); SET pos := 0; SET letterCt := 0; SELECT LENGTH(inStr) INTO strLen; ctLoop: LOOP IF (strLen = 0) THEN LEAVE ctLoop; END IF; SELECT SUBSTR(inStr, strLen, 1) INTO curLetter; IF (ASCII(curLetter) >= 65 AND ASCII(curLetter) <= 90) OR (ASCII(curLetter) >= 97 AND ASCII(curLetter) <= 122) THEN SET letterCt := letterCt + 1; END IF; SET strLen := strLen - 1; END LOOP ctLoop; RETURN letterCt; END // DELIMITER ; select numberOfLetters('abc123 def'); 

Output:

 +-------------------------------+ | numberOfLetters('abc123 def') | +-------------------------------+ | 6 | +-------------------------------+ 

Answer 3

you can just use the length () function:

---

SELECT length ('abc123 def');

he returns: 10

Answer 4

I can't say this is good for MySQL, but just try -

 SELECT CHAR_LENGTH( REPLACE( REPLACE( REPLACE( REPLACE('abc123 def', ' ', ''), '1', ''), '2', ''), '3', '') ); 

... you must add the REPLACE function for each char that you want to remove from the count.