I am trying to search a complex string from CLOB data in DB2. My Search String is - "salary":"10000"
And my query is below -
where PRINT_DATA is a CLOB column in Employee table.
when i run this query on DB2 Visualizer tool, it is giving me correct records with data. But when i run the same query via Linux on my DB server , it is returning 0 records. Does anyone has any idea of this different behaviour from Linux machine ?
I tried to use the escape character as well ("salary"\:"10000") but no luck. It is very strange that same query is working on DB2 Visualizer tool but not working when i am executing this on Linux machine
You must use correct escaping.
Your shell "eats" double quotes otherwise.
Shell tests:
# No double quotes in the constant!
$ echo db2 "select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '"salary":"10000"')"
db2 select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, 'salary:10000')
$ echo db2 "select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '\"salary\":\"10000\"')"
db2 select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '"salary":"10000"')
Db2 tests:
db2 "create table employee (print_data clob (100k))"
db2 "insert into employee_my (print_data) values ('abc \"salary\":\"10000\" xyz')"
db2 "select print_data::varchar(80) from employee_my"
1
------------------------
abc "salary":"10000" xyz
# Correct escaping == correct result
db2 "select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '\"salary\":\"10000\"')"
1
------------------------
abc "salary":"10000" xyz
# No escaping == wrong result
db2 "select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '"salary":"10000"')"
1
-----------------------
0 record(s) selected.
# Wrong escaping == wrong result
db2 "select print_data::varchar(80) from employee_my where dbms_lob.instr(print_data, '"salary"\:"10000"')"
1
-----------------------
0 record(s) selected.