MVSFORUMS.com

[mayuri]

hi all,

A DB2 table column has a value like ABCD QWER.
when i extracted this value and wrote into a file it was written as ABCD QWER only.

The . is actually x'00' and i found this by doing HEX ON in the SPUFI by executing a query which gives me this row.

Now i want to find out how many rows are present in the table where the value in this column ends with x'00'.

select count(column) from table where column like '%.' will not work because '.' is x'4B'.

can anyone help me with the query please.

Thanks in advance

Mayuri
[/quote]

[dbzTHEdinosauer]

try select count(column) where HEX(RIGHT(column,1)) = x'00'
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

mayuri,

You can specify the search string in hex along with the %(x'6c') sign in hex. Try this

[mayuri]

hi all,
i got it with the following query:
select count(column) from table where RIGHT(column,1) = X'00'

also the query given by Kolusu.

Thanks dbzTHEdinosauer and Kolusu for your inputs.

Regards
Mayuri

[mayuri]

hi all
i have got one more query on LIKE search. i have few rows where " is present in one of the column value.
i have queried as follows
select count(*) from table where column like '%"%';

the resutl was 22

but when i queried using the following query
select count(*) from table where column like X'7F';

i'm getting result as 142

can anybody tell me the problem. is the second query right?

Thanks in advance

Regards
Mayuri A

[dbzTHEdinosauer]

only thing I can think of is that the double-quote is possibly being interpreted as an escape, thus the first query returns the number of columns with a percent sign and the second query returns the number of columns with a double-quote as the first char. but, since I don't have a db2 immediately available, I can't play. so, these are only guesses.

have fun, would like to know the answer.
_________________
Dick Brenholtz
American living in Varel, Germany

[vkphani]

Your second query gives the count where the column value is ". First query gives the count even if you have '.''.' as the value.

So, your second query should be the correct one.

[dbzTHEdinosauer]

mayuri,

since we are only talking about 164 items, did you select, display and interrogate the returned columns? you could then answer the question yourself and provide an answer to us. thx
_________________
Dick Brenholtz
American living in Varel, Germany

[mayuri]

hi all,

thanks for your responses. firstly i would like to apologize for the reason that the output of my second query is not 142 but zero.

anyways the first query is the correct one.

Regards
Mayuri A