CODEWITHSUNDEEP
sqlregexoracle-database
NDubonix
NDubonix

Reputation: 103

Oracle RegExp_Like for Two Repeating Digits

I would like to write a regexp_like function which would identify if a string consists of two repeating characters. It would only identify a string that has alternating numbers and only consisting of two unique numbers, but the unique number cannot repeat, it must alternate.

Requirement :

Regular expression should match the pattern for 787878787, but it should NOT match the pattern 787878788 It should NOT consider the pattern like 000000000

Upvotes: 2

Views: 3411

Answers (3)

David Faber
David Faber

Reputation: 12485

I think you want the following:

WITH t1 AS (
    SELECT '787878787' AS str FROM dual
     UNION
    SELECT '787878788' AS str FROM dual
     UNION
    SELECT '7878787878' AS str FROM dual
     UNION
    SELECT '78' AS str FROM dual
)
SELECT * FROM t1
 WHERE REGEXP_LIKE(str, '^(.)(.)(\1\2)*\1?$')
   AND SUBSTR(str, 1, 1) != SUBSTR(str, 2, 1)

This will cover the case (mentioned in the requirements) where the string ends with the same character with which it begins. If you want only digits, replace the . in the regex with \d.

Update:

Here is how the regex breaks down:

^ = start of string
(.) = first character - can be anything - in parentheses to capture it and use it in a backreference
(.) = second character - can be anything
\1 = backreference to first captured group
\2 = backreference to second captured group
(\1\2)* = These should appear together zero or more times
\1? = The first captured group should appear zero or one times
$ = end of the string

Hope this helps.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You might do something like this -

SQL> WITH DATA AS(
  2  SELECT '787878787' str FROM dual UNION ALL
  3  SELECT '787878788' FROM dual
  4  )
  5  SELECT *
  6  FROM DATA
  7  WHERE REGEXP_LIKE(str, '(\d+?)\1')
  8  AND SUBSTR(str, 1,1) = SUBSTR(str, -1, 1)
  9  /

STR
---------
787878787

SQL>

Since you are dealing only with digits, I used \d.

\d+? will match the digits, and, \1 are the captured digits. The substr in the AND condition is checking whether the first and last digit of the string are same.

Edit : Additional requirement by OP

To avoid the numbers like 00000000, you need to add a NOT condition to the predicate.

SQL> WITH DATA AS
  2    ( SELECT '787878787' str FROM dual
  3    UNION ALL
  4    SELECT '787878788' FROM dual
  5    UNION ALL
  6    SELECT '787878788' FROM dual
  7    )
  8  SELECT *
  9  FROM DATA
 10  WHERE REGEXP_LIKE(str, '(\d+?)\1')
 11  AND SUBSTR(str, 1,1)  = SUBSTR(str, -1, 1)
 12  AND SUBSTR(str, 2,1) <> SUBSTR(str, -1, 1)
 13  /

STR
---------
787878787

SQL>

Upvotes: 1

gwillie
gwillie

Reputation: 1899

You could try:

^(..)\1*$

Breakdown:

^    - assert beginning of line
(..) - capture the first 2 characters
\1*  - repeat the captured group pattern zero or more times
$    - assert end of line

Untested in oracle...

Upvotes: 0

Related Questions