英文:
Finding only values which are consecutive identical characters
问题
我有一列数据，我想只提取那些只包含连续相同字符的值，例如 'ppp'、'mmmm'、'ddddd'。字符串的长度将大于或等于3。
我尝试了不同的查询，但不知何故它没有给我输出。
英文:
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
答案1
得分: 1
你可以应用一些技巧。首先，使用LEFT字符串函数提取第一个字符。然后，使用TRIM、REPLACE或TRANSLATE函数将该字符的出现替换为空值。验证结果字符串是否为空。最后，使用LENGTH（或LEN）函数验证字符串的长度。考虑以下SQL查询，它们采用了这些技巧：
SELECT name
FROM items
WHERE TRIM(LEFT(name, 1) FROM name) = '' AND LENGTH(name) >= 3
SELECT name
FROM items
WHERE REPLACE(name, LEFT(name, 1), '') = '' AND LENGTH(name) >= 3
我已经在MySQL中测试过这个想法，它的效果符合预期：
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
-- 输出:
-- gggggggggg
-- CCCC
-- ffffff
此外，如果你的SQL引擎支持正则表达式和捕获组，你可以使用像(.)\1{2,}这样的模式来实现它。即使不支持捕获组，你仍然可以将LEFT函数与正则表达式模式结合起来实现所需的结果。
英文:
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 queries, which incorporate these techniques:
SELECT name
FROM items
WHERE TRIM(LEFT(name, 1) FROM name) = '' AND LENGTH(name) >= 3
<!-- separator -->
SELECT name
FROM items
WHERE REPLACE(name, LEFT(name, 1), '') = '' 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.
答案2
得分: 0
一个替代方法是使用递归查询将每个字符串拆分为单个字符，然后根据不同字符的计数进行聚合和过滤。
假设您的表的结构类似于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(car) >= 3 and count(distinct car) = 1
请注意，递归查询仅在版本8.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(car) >= 3 and count(distinct car) = 1
Note that recursive queries are supported in version 8.0 only.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论