SQL ORACLE using %VALUE%

user_14576164 0
Viewed 38

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 '%12345%';

--> 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%;
oracle sql
2 Answers

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
user_137555380

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 '%12345%' 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;
user_129305520
Related