删除数字重复大于5的记录 [英] Deleting records with number repeating more than 5

问题描述

我有一个长度为9的数据表，数据类似

  999999969 
 000000089 
 666666689 
  

我想只删除1-9中的任何数字重复大于5的数据

解决方案

使用 返回参考 。

  DELETE FROM tbl 
 WHERE col〜'（[1-9]）\1 {5}'; 
 
 
 
 $ b  
 h3> 
 
 
 （[1-9]） ...数字从1到9的字符类，返回引用。
 
  \1  ...返回引用第一个（且仅在此情况下）括号括起的子表达式。
 
  {5}  ..完全（另一个）5次，使其超过5。
 
 
 
 每个文档： 
 
 
后退引用（ \\\
 ）匹配与前一个匹配的相同字符串
括号子表达式由  n  指定[...]例如，（[bc]）\1 匹配 bb 或 cc ，但不匹配 bc 或 cb 。
 
 
  SQL Fiddle演示。
 
I have data in a table of length 9 where data is like
999999969
000000089
666666689
I want to delete only those data in which any number from 1-9 is repeating more than 5 times.
解决方案
This can be much simpler with a regular expression using a back reference. 
DELETE FROM tbl
WHERE  col ~ '([1-9])\1{5}';
That's all.

Explain

([1-9]) ...  a character class with digits from 1 to 9, parenthesized for the following back reference.

\1 ... back reference to first (and only in this case) parenthesized subexpression.

{5} .. exactly (another) 5 times, making it "more than 5".

Per documentation:

  
A back reference (\n) matches the same string matched by the previous
  parenthesized subexpression specified by the number n [...] For example, ([bc])\1 matches bb or cc but not bc or cb.
SQL Fiddle demo.

                        
这篇关于删除数字重复大于5的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！