LIKE in IBM Informix SQL

user_5660493 0
Viewed 376

I have a varchar column "qty" defined in a table "order" which has 2 rows of data as follows

qty
3
3 1/9

When I try to use select query as

select * from order where qty like '3 %';

both rows which have qty 3 and 3 1/9 are returned, whereas I thought I would get only 3 1/9 which matches my lookup in like '3 %', I even tried with trim(qty) like '3 %' also with the same result; I get both records. There is no space after 3 in the first row.

Not sure why? Can someone help me here?

informix
2 Answers

Try: select * from order where qty like '3_%'.

user_91227190

In Java, there is a parameter you can add to your jdbc url: ;IFX_TRIMTRAILINGSPACES=0

In example:

jdbc:informix-sqli://dbhost:service/database:INFORMIXSERVER=ifxserver;IFX_LOCK_MODE_WAIT=1;IFX_TRIMTRAILINGSPACES=0

Hopefully, it will make the LIKE work the way you want

user_145603390
Related