Experiences in using like in DB2 Databases

This article will introduce some experiences of like in DB2, with examples for your reference, and hope to help you.

The use of like in DB2 is restricted. It cannot be followed by a variable or field. Currently, many variables are like '% test %'. Therefore, in a function or stored procedure, you cannot use a form parameter to form a dynamic condition for like.

If you want to search a field for a string in a function or stored procedure, you can use locate. The usage of locate is as follows:

Locate (arg1, arg2, <pos>): searches for the location where arg1 first appeared in arg2. If you specify pos, you can find the location where arg1 first appeared at the pos of arg2. The third parameter is optional)

The following is an example of using this method:

Create function gdzj. GETNOPASS
(Itemname varchar (100 ),
Reporttype integer,
RESULTID INTEGER
)
RETURNS INTEGER
Specific gdzj. SQL091102183311900
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
Begin atomic
-- Return Value 0: This check item does not exist. 1: check items are available, but all items are qualified. 2: check items exist, but one is unqualified. If one fails, the system determines that it is unqualified)
DECLARE IResult INTEGER;
DECLARE ICount INTEGER;
Set IResult = 0;
-- REPORTTYPE: 1: external unit, 0: Non-External Unit
If REPORTTYPE = 1 then
Set ICount = (
Select count (1) from DETECT_END_INFO where
CHECK_OUT_UNIT_INFO_ID = RESULTID and locate (ITEMNAME, PROJECT_NAME)> 0
End;