To check if specified pattern is repeating through the entire length of string

Question

I am trying to match a pattern which is like '12345@5.6;12345@45;12345@0.5'.I am trying to Oracle(11g) REGEXP_LIKE function to do that. Here is my code-

SET SERVEROUTPUT ON;

Begin

if regexp_like( TRIM('12345@5.6;12345@45;12345@0.5'),'[^\d+@((\d+.\d{0,4})|(\d+));$]+')

then
dbms_output.put_line('yes');
else 
dbms_output.put_line('No');
end if;

end;

For the above code output is 'Yes' which is what i wanted.But this code is also returning 'yes' for pattern like '12345@5.6,12345@45;12345@0.5'(instead of semi colon I specified comma after '5.6').

It is basically checking for the pattern but if it finds at least one pattern of this kind this is returning true and not checking for the remaining string to make sure everything is in the pattern.

I want only patterns of the specified kind in the entire length of the string.If something is out of pattern I need to return 'No'.

Another eg:- For suppose there is a string 'abc;abc;abc' I want to check if pattern 'abc' not only exists in the string but also repeats itself through entire string. That means code should return false for strings like 'abc;bca;def' and should return true only for 'abc;abc;abc'.

To be clear,I just want to check if specified pattern is repeating through the entire length of string else I want to return 'No',not just see at least one exists and return true and I do not know how many times this pattern exist.

Hope I am clear,Please help.Thank you.

Your description is really not so clear. Try `'^[0-9]+@[0-9]+(\.[0-9]{0,4})?(;[0-9]+@[0-9]+(\.[0-9]{0,4})?)+$'`. If it works for you, I will post with explanation. See [here how it works](https://regex101.com/r/y1y4hg/1). — Wiktor Stribiżew, Dec 07 '16 at 17:01
I don't see a repeating pattern in your first example. You need to give a much more precise definition of "pattern" and "match". — , Dec 07 '16 at 17:53
@Tejaduggirala - OK, that makes much more sense. I assume by "number" you mean non-negative number (minus sign not allowed), and it must be in base 10 (no hexadecimal numbers, no scientific notation using exponents). How about decimals... is .33 allowed, or does it have to be 0.33 in that case? Also, is 1. allowed (the integer 1)? Oracle allows both .33 and 1. as numbers - does your business requirement? — , Dec 07 '16 at 18:45
@mathguy The pattern I want in the string is (number@number or decimal;) like '12456@45;12345@4.3;' or '12345@45;5432@3.2;6785@34' the ending can be semi colon or without semi colon. my question is that with my regex expression when using REGEXP_LIKE on the string '12345@34,34567@4.5;45678@22' is returning true which it should not because after '34' it has ',' and not ';' but my regex expression is returning true because in this string '12345@34,34567@4.5;45678@22', '34567@4.5;' is matching my expression.I need my regex to match only if string has repeating 'somenumber@somenumber or decimal;' — Teja duggirala, Dec 07 '16 at 18:46
THAT question is easy (why your code doesn't work). There are several problems; the one that causes the immediate wrong result is using `[ ... | ... ]` where you probably meant `( ... | ... )` for alternation. `[...]` is for matching any character within the square brackets. — , Dec 07 '16 at 18:51
@mathguy Thank you for your time.Yes,I think I used them wrong. — Teja duggirala, Dec 07 '16 at 18:54
@WiktorStribiżew Hi, thank you for your time.Your expression works for me.Please post some explanation. — Teja duggirala, Dec 07 '16 at 18:56
I posted the answer, please check and let know if anything is unclear. — Wiktor Stribiżew, Dec 07 '16 at 19:11

score 1 · Accepted Answer · answered Dec 07 '16 at 19:10

You may use

'^[0-9]+@[0-9]+(\.[0-9]{0,4})?(;[0-9]+@[0-9]+(\.[0-9]{0,4})?‌)+$'

See the regex demo

Your main building block in this regex is [0-9]+@[0-9]+(\.[0-9]{0,4})? - one or more digits, @, 1+ digits, and then an optional (see the (...)? grouping construct with the ? quantifier that matches 1 or 0 occurrences) sequence of a dot (\.) and 0 to 4 digits ([0-9]{0,4} - if you do not want to allow a dot with no digits afterwards, replace 0 with 1).

Then, you want to validate the entire string consisting of these blocks with a semi-colon as a separator. You need to use

      ^    +   block   + (      ;    + block  +  )       +         $
      |                  |      |                |       |         |
    string             group   sep             group 1 or more   string 
    start              start                    end  occurrences  end

score 0 · Answer 2 · answered Dec 07 '16 at 18:58

I think this should work:

with
     inputs ( str ) as (
       select '12345@5.6;12345@45;12345@0.5' from dual union all
       select '12345@5.6;12345@45,12345@0.5' from dual
     )
select str,
       case when regexp_like(str, '^(\d+@(\d+|\d*.\d+)(;|$))+$') then 'valid'
            else 'invalid' end
       as   result
from   inputs
;


STR                           RESULT
----------------------------  -------
12345@5.6;12345@45;12345@0.5  valid
12345@5.6;12345@45,12345@0.5  invalid

The ^ at the beginning and the $ at the end guarantee that the entire string must be matched (not any substring from it, but ALL of it). The second-to-last character, +, in the matching pattern requires one or more repetitions of the "pattern". You understand the \d+@ portion. Then there are two alternations - to allow an integer or a decimal, and to end either with a semicolon or with the end of the string.

This does not allow something like 3. as the "second part" of the pattern. Also, if your "second part" is meant to be an IP address, which usually has four parts, not two, the matching pattern will need to be adjusted. Please write back if you need help with that.

To check if specified pattern is repeating through the entire length of string

2 Answers2