0

I would like to rewrite the below Oracle REGEXP_COUNT function into MariaDB 10.3. I am trying to rewrite using LENGTH concept but couldn't get valid result.

I am using the ^((((https?|ftps?|gopher|telnet|nntp):\/\/)|(mailto:|news:))(%[0-9A-Fa-f]{2}|[-()_.!~*\'';/?:@&=+$,A-Za-z0-9])+)([).!/?:,][[:blank:]])?$ pattern and the code like this:

SET SQL_MODE=ORACLE;
DELIMITER //
CREATE OR REPLACE FUNCTION "IS_VALID"(VALUE TEXT) RETURN double
IS
    is_valid DECIMAL (1, 0);
BEGIN
    is_valid := 0;
      IF  REGEXP_COUNT(VALUE, '^((((https?|ftps?|gopher|telnet|nntp):\/\/)|(mailto:|news:))(%[0-9A-Fa-f]{2}|[-()_.!~*\'';/?:@&=+$,A-Za-z0-9])+)([).!/?:,][[:blank:]])?$') = 1
      THEN
        is_valid := 1;
      END IF
    RETURN is_valid;
END;//
DELIMITER ;
halfer
  • 19,824
  • 17
  • 99
  • 186
Raam
  • 39
  • 1
  • 8
  • What do you mean by "confused`? You seem to have a quoting error in the SQL code. – tripleee Feb 19 '20 at 18:45
  • Well, `'^((((https?|ftps?|gopher|telnet|nntp)://)|(mailto:|news:))(%[0-9A-Fa-f]{2}|[-()_.!~*'';/?:@&=+$,A-Za-z0-9])+)([).!/?:,][[:blank:]])?$'` should work. – Wiktor Stribiżew Feb 19 '20 at 22:13
  • @Wiktor, Yes it worked. The main concern is REGEXP_COUNT. In mariadb we don't have the function REGEXP_COUNT. so please lemme know what is the alternate method to get the count the number of times a specific pattern is found in the string. I even tried using LENGTH concept, but didn't work for me. – Raam Feb 20 '20 at 06:30
  • whether REGEXP in MariaDB could suffice the need to count the number of times a specific pattern is found in the string? – Raam Feb 20 '20 at 06:37
  • So why do you say *I am trying to rewrite using REGEXP but couldn't get valid result*? You should update the question with the real problem statement. – Wiktor Stribiżew Feb 20 '20 at 08:35
  • @Wiktor, Sorry I overlooked on that. I have corrected the question part. – Raam Feb 20 '20 at 10:53
  • please let me know, does REGEXP helps me to get the valid result. – Raam Feb 20 '20 at 10:57

0 Answers0