CODEWITHSUNDEEP
mysql
Graham
Graham

Reputation: 8181

mysql - count letters

Can anyone tell me how to count the letters in a string in MySQL?

For example:

SELECT numberOfLetters('abc123 def')

would return 6

By letter, I mean A-Z and a-z.

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

The version of MySQL that I'm using is 5.5.27

Upvotes: 5

Views: 3179

Answers (4)

Hala
Hala

Reputation: 21

you can simply use length() function like that:

SELECT length('abc123 def');

it returns : 10

Upvotes: 1

StudyOfCrying
StudyOfCrying

Reputation: 530

Fairly off the top of my head, but I believe this will do what you need.

NOTE: Assuming ASCII and that you're only counting a-z, A-Z.

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

Upvotes: 1

eisberg
eisberg

Reputation: 3771

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');

Upvotes: 3

Devart
Devart

Reputation: 122042

I cannot say that this is good for MySQL, but just try it -

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

...you should add REPLACE function for each char you need to remove from counting.

Upvotes: 0

Related Questions