regular expression number appear any place-CodePudding

I want to write a regular expression using oracle that makes it true that 1 appears twice in any position.

1234 -> false
1123 -> true
2234 -> false
2311 -> true
1231 -> true

I tried writing

/[1]{2}

but this cannot contain 1231.

CodePudding user response：

You can use a combination of functions such as

SELECT DECODE(REGEXP_REPLACE(col,'[^1]'),11,'true','false') AS count_of_the_ones
  FROM t

Demo

CodePudding user response：

As you said - true if there are two 1s in the string. So count them:

Sample data:

SQL> with test (col) as
  2    (select '1234' from dual union all
  3     select '1123' from dual union all
  4     select '2234' from dual union all
  5     select '2311' from dual union all
  6     select '1231' from dual
  7    )

Query begins here:

  8  select col,
  9    case when regexp_count(col, '1') = 2 then 'true'
 10         else 'false'
 11    end result
 12  from test;

COL  RESULT
---- --------------------
1234 false
1123 true
2234 false
2311 true
1231 true

SQL>