SQL ORACLE using %VALUE%-CodePudding

Hi because of technical limitation of a Framework I need another way to do a smiliar query without using single quotes

--> current

 Select Json_Value(json, '$.bankReference') From R_O where json like '345%';

--> Need a Valid query without single quotes which is doing exactly the same thing, maybe a function or something.

 Select Json_Value(json, '$.bankReference') From R_O where json like %12345%;

CodePudding user response：

A similar alternative, but a little bit more dynamic

Demo

SQL> create table t1 ( c1 varchar2(10) ) ;

Table created.

SQL> insert into t1 values ( 'A12345B' );

1 row created.

SQL> insert into t1 values ( 'A12345C' );

1 row created.

SQL> insert into t1 values ( 'A12345D' );

1 row created.

SQL> insert into t1 values ( 'A12399B' );

1 row created.

SQL> insert into t1 values ( 'A13299B' );

1 row created.

SQL> insert into t1 values ( 'A21399B' );

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from t1 ;

C1
----------
A12345B
A12345C
A12345D
A12399B
A13299B
A21399B

6 rows selected.

Now let's create a function that admits two parameters:

The column we want to check
The value we want to apply the % ( I am guessing that is always a number ). If the value contains any string, it won't work.

Function

SQL> create or replace function p_chk_json(p_text varchar2, p_val number)
return integer is
begin
  if p_text like '%'||p_val||'%' then 
   return 1;
  else
    return 0;
  end if;
end;
/

Function created.

Then test it for 12345 or 99

SQL> select * from t1 where p_chk_json(c1 , 12345) = 1;

C1
----------
A12345B
A12345C
A12345D

SQL> select * from t1 where p_chk_json(c1 , 99 ) = 1 ;

C1
----------
A12399B
A13299B
A21399B

CodePudding user response：

In Oracle you can write a function something like that:

create or replace function test1(p_text varchar2)
  
  return integer is
  
begin
  if p_text like '345%' then
    return 1;
  else
    return 0;
  end if;
end test1;

Your modified SQL statement will be:

Select Json_Value(json, '$.bankReference') From R_O where test1(json) = 1;