Hi,

I have to search in a table for a specific detail name for that I have written the following query.

SELECT * FROM MA1.T5420_COMM_DETAIL WHERE COMMUN_DTL_NM='Optima Credit Card ';

Whnever I am enetring the value of commun_dtl_nm without those extra spaces I am not able to search that name in the database.

I have tried following as well

SELECT * FROM MA1.T5420_comm_detail where
LTRIM(RTRIM(COMMUN_DTL_NM))=
RTRIM(LTRIM('Optima Credit Card ')) WITH UR;


In this case also I was not able to get any matching rows when ever I was entering the commun_dtl_nm without ane extra spaces.


Please help me to achiev this.

Thanks
Arun

have you tried where
COMMUN_DTL_NM like 'Optima Credit Card%';

Hi,

Thanks for the quick reply.

I would have used it, but my requirenment is to match the exact string rather than matching a similar string.

Thanks
Arun

What's the exact definition of your table "MA1.T5420_comm_detail"? Maybe the column in question is defined as CHAR and VARCHAR would be better?

Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

I am not able to comare the values, if I am taking only text without spaces.

Thanks
Arun

Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

I am not able to comare the values, if I am taking only text without spaces.

Thanks
Arun

Maybe I'm missing something, but if you have the trailing spaces in the value stored in the table, it is mandatory that DB2 takes those spaces into consideration during string comparison. After all, those spaces are part of the string value and you can't expect that a comparison only considers a part of the value.

As for the 2nd query, could you shown us the result from an "SELECT '@' || COMMUN_DTL_NM || '@' FROM MA1.T5420_comm_detail"? I'm asking because you may not have exactly the value "Optima Credit Card" in your table. Maybe you assume a case-insensitive string comparison, too?

trailing spaces are not taken into account
where col='xxxx ' is the same as col='xxxx '
you can put as many spaces as you want- only 1 is taken into account
look at the sample
[09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select * from var where col1='xxx '" <-- with 1 space
COL1
----------
xxx
xxx

2 record(s) selected.

[09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select * from var where col1='xxx '" <-- with 2 spaces
COL1
----------
xxx
xxx

2 record(s) selected.

[09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select col1,hex(col1) from var"

COL1 2
---------- --------------------
xxx 78787820
xxx 787878202020

2 record(s) selected.

I stand corrected. Here is what the DB2 manual (http://publib.boulder.ibm.com/infoce.../r0008479.html
says:
So I would say that the OP has a different problem, and it would be good to get the answer to my 2nd question.