Question 197041 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Finding only values which are consecutive identical characters

I have a column from which I would like to retreive only those values which are only consecutive identical characters like ‘ppp’ ‘mmmm’, ‘ddddd’. The length of the string will be greater than or equals to 3

I tried different queries but somehow it’s not giving me the output

Tags:

2

Answers


  1. 0

    One alternative uses a recursive query to split each string to individual characters, then aggregates and filters on the count of distinct characters.

    Assuming that your table has a structure like mytable(id, str):

    with recursive cte as (
    select id, str, 
        char_length(str) as max_pos
        1                as pos,
        null             as car 
    from mytable
    union all 
    select id, str, max_pos, pos + 1, substring(str, pos, 1)
    from cte 
    where pos <= max_pos
)
select id, str
from cte
group by id, str
having count(distinct car) = 1

    Note that recursive queries are supported in version 8.0 only.

    Login or Signup to reply.
  2. 0

    You can apply some tricks. Begin by using the LEFT string function to extract the first character. Then, replace occurrences of that character with an empty value using either the TRIM, REPLACE, or TRANSLATE functions. Verify that the resulting string is empty. Finally, validate the length of the string using the LENGTH (or LEN) function. Consider the following SQL query, which incorporates these techniques:

    SELECT name
FROM items
WHERE TRIM(LEFT(name, 1) FROM name) = '' AND LENGTH(name) >= 3

    I have tested the idea with MySQL, and it worked as expected:

    CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

INSERT INTO items VALUES
  (1, 'abcd'),
  (2, 'aa'),
  (3, 'gggggggggg'),
  (4, 'CCCC'),
  (5, 'k'),
  (6, 'ffffff');

SELECT name
FROM items
WHERE TRIM(LEFT(name, 1) FROM name) = '' AND LENGTH(name) >= 3

-- Output:
-- gggggggggg
-- CCCC
-- ffffff

    Additionally, if your SQL engine supports regex and capturing groups, you can implement it using patterns such as (.)1{2,}. Even if capturing groups is not supported, you can still combine the LEFT function with regex patterns to achieve the desired result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search