How to perform a query for a string which contains an "&"?

[Harry Siemer]

I am searching a table column for a value 'Policy & Procedures'
The "&" causes the query to fail.

[yarbrough40]

use brackets [Policy & Procedures]

[vijay2082]

Hi,

Use escape construct in your query.

C:\backup>db2 "insert into db2admin.mytable values('kunal & vijay')"
DB20000I The SQL command completed successfully.

C:\backup>db2 select * from db2admin.mytable

NAME
------------------------------
ajay
shubhangi
kunal
SHUBHANGI
kunal & vijay

5 record(s) selected.

> below query will return an error as expected

C:\backup>db2 select * from db2admin.mytable where name like '%&%'
SQL0010N The string constant beginning with "'%" does not have an ending
string delimiter. SQLSTATE=42603
'%'' is not recognized as an internal or external command,
operable program or batch file.

> Use escape character to make your search

C:\backup>db2 "select * from db2admin.mytable where name like '%&&%' escape '&' "

NAME
------------------------------
kunal & vijay

1 record(s) selected.


C:\backup>db2 "insert into db2admin.mytable values('kunal _ vijay')"
DB20000I The SQL command completed successfully.

C:\backup>db2 select * from db2admin.mytable

NAME
------------------------------
ajay
shubhangi
kunal
SHUBHANGI
kunal & vijay
kunal _ vijay

6 record(s) selected.

C:\backup>db2 "select * from db2admin.mytable where name like '%__%' escape '_' "

NAME
------------------------------
kunal _ vijay

1 record(s) selected.


C:\backup>db2 "insert into db2admin.mytable values('kunal / vijay')"
DB20000I The SQL command completed successfully.

C:\backup>db2 "select * from db2admin.mytable where name like '%//%' escape '/' "

NAME
------------------------------
kunal / vijay

1 record(s) selected.

Cheers, Vijay