REGEXP to validate a specific number

user_15437323 0
Viewed 63

How can I search for a specific number in an array using REGEXP? I have an array and need to verify if it has a specific number.
Ex: [5,2,1,4,6,19] and I am looking for number 1, but just the number 1 and not any number that contain the digit 1.


I had to do this:

case when REGEXP_INSTR(JSON_QUERY(MY_JSON_COLUMN,'$.path') , '[[]{1}[1][,]')<>0
            or REGEXP_INSTR(JSON_QUERY(MY_JSON_COLUMN,'$.path') , '[,]{1}[1][,]{1}')<>0
            or REGEXP_INSTR(JSON_QUERY(MY_JSON_COLUMN,'$.path') , '[,]{1}[1][]]')<>0
            or REGEXP_INSTR(JSON_QUERY(MY_JSON_COLUMN,'$.path') , '[[]{1}[1][]]') <>0 
      then 'DIGIT_ONE' else 'NO_DIGIT_ONE' 
 end

Is there anything simpler?

oracle sql json
3 Answers

You can use

(^|\D)1(\D|$)

This will seach for 1 not enclosed with other digits.

See this regex demo.

Details

  • (^|\D) - start of string or non-digit
  • 1 - a 1 char
  • (\D|$) - non-digit or end of string.
user_38329700

Alternatively, with a little bit more typing (a little bit? Am I kidding?!), splitting the string into rows and comparing values to the search string:

SQL> with test (col) as
  2    (select '[5,2,1,4,6,19]' from dual)
  3  select t.col,
  4    case when '&par_search_string' in
  5              (select regexp_substr(substr(col, 2, length(col) - 1), '[^,]+', 1, level) val
  6               from test
  7               connect by level <= regexp_count(col, ',') + 1
  8              )
  9         then 'Search string exists'
 10         else 'Search string does not exist'
 11    end result
 12  from test t;
Enter value for par_search_string: 1

COL            RESULT
-------------- ----------------------------
[5,2,1,4,6,19] Search string exists

SQL> /
Enter value for par_search_string: 24

COL            RESULT
-------------- ----------------------------
[5,2,1,4,6,19] Search string does not exist

SQL>
user_90979060

Do NOT use regular expressions, use a proper JSON parser and then filter for the number you want:

SELECT my_json_column,
       CASE
       WHEN  JSON_EXISTS( my_json_column, '$?(@.path[*] == 1)' )
       THEN 'DIGIT ONE'
       ELSE 'NO DIGIT ONE'
       END AS has_one
FROM   table_name;

or (if you are using Oracle 12.1 and cannot use path filter expressions with JSON_EXISTS, which is only available from Oracle 12.2):

SELECT my_json_column,
       CASE
       WHEN EXISTS(
              SELECT 'X'
              FROM   JSON_TABLE(
                       t.my_json_column,
                       '$.path[*]'
                       COLUMNS (
                         value NUMBER PATH '$'
                       )
                     )
              WHERE  value = 1
           )
       THEN 'DIGIT ONE'
       ELSE 'NO DIGIT ONE'
       END
FROM   table_name t;

Which, for the sample data:

CREATE TABLE table_name (
  my_json_column CHECK ( my_json_column IS JSON )
) AS
SELECT '{"path":[5,2,1,4,6,19],"not_this_path":[1,2,3,4,5]}' FROM DUAL UNION ALL
SELECT '{"path":[5,2,4,6,19],"not_this_path":[1,2,3,4,5]}' FROM DUAL UNION ALL
SELECT '{"path":[11],"not_this_path":[1]}' FROM DUAL UNION ALL
SELECT '{"path":[2],"not_this_path":[1]}' FROM DUAL UNION ALL
SELECT '{"path":[1,11]}' FROM DUAL;

Both output:

MY_JSON_COLUMN                                      | HAS_ONE     
:-------------------------------------------------- | :-----------
{"path":[5,2,1,4,6,19],"not_this_path":[1,2,3,4,5]} | DIGIT ONE   
{"path":[5,2,4,6,19],"not_this_path":[1,2,3,4,5]}   | NO DIGIT ONE
{"path":[11],"not_this_path":[1]}                   | NO DIGIT ONE
{"path":[2],"not_this_path":[1]}                    | NO DIGIT ONE
{"path":[1,11]}                                     | DIGIT ONE

db<>fiddle here

user_15092640
Related