CODEWITHSUNDEEP
mysqlsql
Random User
Random User

Reputation: 365

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

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

Upvotes: 0

Views: 587

Answers (4)

Chetan Oswal
Chetan Oswal

Reputation: 561

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

Upvotes: 0

peterm
peterm

Reputation: 92815

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

Upvotes: 0

Sico
Sico

Reputation: 1183

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

Upvotes: 1

Nitin Varpe
Nitin Varpe

Reputation: 10704

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'

Upvotes: 0

Related Questions