Selecting values with more than one occurrence of a character in SQL

Question

Let me explain my question with an example

Consider the following column of values

City
-------
Chennai
Delhi
Mumbai

Output I want is

City
-------
Chennai
Mumbai

When you look at the values 'Chennai' has two 'N's and 'Mumbai' has two 'M's

What is the query to find the values that satisfy the above said condition

I am using MySQL

@Dineshbabu Can you let us know the exact condition means any string repeating a char twice of more it should come, what if two characters satisfies the condition like `hyderabad` a twice.. d twice.. — Vishwanath Dalvi, Nov 23 '13 at 07:20
Actually just the character **a** sorry for confusing you guys — Random User, Nov 23 '13 at 07:25

score 1 · Answer 1 · edited May 23 '17 at 12:19

1

You may be able to use some of the logic from here and then filter that way Count all occurances of different characters in a column

edited May 23 '17 at 12:19

Community

1
1

answered Nov 23 '13 at 07:13

Sico

1,183
1
10
16

score 0 · Answer 2 · answered Nov 23 '13 at 07:12

0

Can u try this. If you want you can create function and accepts dynamic value and pass to the corresponding function

IF(LEN('Chennai')-LEN(REPLACE('Chennai', 'N', ''))>1 )
 Select 'Chennai'

answered Nov 23 '13 at 07:12

Nitin Varpe

10,450
6
36
60

score 0 · Answer 3 · answered Nov 23 '13 at 07:29

A possible solution if city names contain only latin characters

SELECT DISTINCT city
 FROM table1 c CROSS JOIN
(
   SELECT 0 n UNION ALL
   SELECT a.N + b.N * 5 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) b
    ORDER BY n
) n
  WHERE CHAR_LENGTH(city) - CHAR_LENGTH(REPLACE(LOWER(city), CHAR(97 + n.n), '')) > 1

Output:

|    CITY |
|---------|
|  Mumbai |
| Chennai |

Here is SQLFiddle demo

@dineshbabu Did it help? Do you need more help with your question? — peterm, Nov 23 '13 at 23:22

score 0 · Answer 4 · answered Oct 22 '21 at 14:27

You can use stored procedure for this. Please check my code -

Create table statement -

CREATE TABLE `Cities` (
  `City` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Added cities to table and created procedure -

CREATE PROCEDURE `SP_SplitString`()
BEGIN
   DECLARE front TEXT DEFAULT NULL;
   DECLARE count INT DEFAULT 0;
   DECLARE arrayText longtext default "";
   DECLARE Value longtext DEFAULT "";
   DECLARE val longtext DEFAULT "";
   DECLARE done INT DEFAULT FALSE;
   DECLARE cityCursor CURSOR FOR SELECT * FROM `Cities`;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   OPEN cityCursor;
   loop_through_rows:
   LOOP  
   FETCH cityCursor INTO Value;

   IF done THEN
      LEAVE loop_through_rows;
   END IF;

   SET val = Value;
   iterator:
   LOOP 
     IF LENGTH(TRIM(val)) = 0 OR val IS NULL THEN
       LEAVE iterator;
     END IF;
     SET front = LOWER(SUBSTRING(val,1,1));


     SET count = LENGTH(Value) - LENGTH(REPLACE(LOWER(Value), front, ''));


     IF count > 1 THEN
       IF LENGTH(TRIM(arrayText)) = 0 THEN
         SET arrayText = Value;
       ELSE
         SET arrayText = CONCAT(arrayText,",",Value);
       END IF;
       LEAVE iterator;
     END IF;
     IF LENGTH(TRIM(val)) > 1 THEN
       SET val = SUBSTRING(val,2,LENGTH(TRIM(val)));
     ELSE
       SET val = "";
     END IF;

   END LOOP;
   END LOOP;

SELECT * FROM `Cities` WHERE FIND_IN_SET(City, arrayText);

END

Selecting values with more than one occurrence of a character in SQL

4 Answers4