It really exists,but I got nothing when select by using the underscore character.

tiger_wen · #1 (**permalink**) 12-23-08, 09:18

The following record exists in tbl01

Code:

key_txt
x'F0F2F1F8F0F0F0F6001A021302A80E2FC3C3F0F1404040404040'

When using the following stmt:

Code:

select * from tbl01 where substr(key_txt,7, 8 ) = x'001A021302A80E2F'

I got:

Code:

KEY_TXT
   02180006........CC01

But when select by using the underscore character:

Code:

 select * from tbl01 where key_txt like 
 x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'

I got nothing.

why,who can help explaining?

Any hints is welcome.Thanks in advance!

stolze · #2 (**permalink**) 12-23-08, 10:40

You are working with DB2 z/OS because your strings appear to be in EBCDIC, right? Have you made sure that your application is also connecting to DB2 in EBCDIC or due you use a different code page perhaps?

Is there a specific reason why you work on binary data instead of regular strings?

Peter.Vanroose · #3 (**permalink**) 12-23-08, 12:58

Quote:

Originally Posted by tiger_wen

select * from tbl01 where key_txt like
x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F140404040 4040'

Shouldn't that be

Code:

select * from tbl01 where key_txt =
 x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'

or otherwise have a wildcard ("%" or "_") in the right hand side.
Note that the wildcard must be there literally, i.e., x'6D' is NOT a wildcard!
Moreover, you may need a "%" at the very end for a match to be found.

tiger_wen · #4 (**permalink**) 12-23-08, 16:00

Thanks all for your kindly help on this.

I should make it more clear:
If I got the key_txt like

Code:

key_txt
x'F0F2F1F8F0F0F0F2001A0213000601B9C3C3F0F1404040404040'

It shows me the same results by using

Code:

select * from ... where substr(key_txt,....... =

&

Code:

select * from ....where key_txt like '0218____....

Why?Does anyone know something about ASCII LIKE or UNICODE LIKE.
Is the ASCII LIKE or UNICODE LIKE the cause?

dav1mo · #5 (**permalink**) 12-24-08, 09:02

I think you are going about this in the wrong manner. You should convert your key_text to hex and then compare it to your string. Here is a query I ran against syspackage table that works fine.

SELECT NAME, HEX(NAME) FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'mycollid'
AND LOCATION = ''
--AND NAME = 'MRM170'
AND HEX(NAME) LIKE '%D9D4F1F7%'

tiger_wen · #6 (**permalink**) 12-25-08, 00:31

Quote:

Originally Posted by dav1mo

I think you are going about this in the wrong manner. You should convert your key_text to hex and then compare it to your string. Here is a query I ran against syspackage table that works fine.

SELECT NAME, HEX(NAME) FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'mycollid'
AND LOCATION = ''
--AND NAME = 'MRM170'
AND HEX(NAME) LIKE '%D9D4F1F7%'

Thanks for your reply!
[DB2 v7 ENCODING SHEMA 0935]
If use LIKE to select records for table,there should no characters such as
x'0E',x'0F',x'6C',x'6D' in pattern expression -- one of the LIKE parm.

In my original post,
Support:We have two record in tbl01
HEX(key_txt)
001A021302A80E2F
001A021302A8252F

If change the x'001A021302A80E2F' to x'001A021302A8122F',and keep
other characters the same.The select stmt with like parm will give us a
record.

If we use

Code:

 select * from tbl01 where key_txt like 
 x'001A021302A86D2F'

It only gives me the x'001A021302A8252F' record.

Thanks everyone so.Thanks a lot.

Peter.Vanroose · #7 (**permalink**) 12-25-08, 12:48

Quote:

DB2 v7 ENCODING SCHEMA 0935

CCSID 935 is a MIXED encoding schema. With DB2 v8, your problem would be simplified since DB2 will convert the character data in your program from the encoding declared by your compiler into 935, hence you don't need to bother with the hexadecimal representation.
With a MIXED encoding scheme, you switch between 2-byte characters and 1-byte characters with the SHIFT-IN and SHIFT-OUT characters (x'0E' and x'0F'). Otherwise said, these two bytes are no characters so indeed they don't match the "_" pattern in a LIKE.
The two wildcards are "%" (x'6C') and "_" (x'6D'). To search for these characters "as is" in a LIKE, you need to escape them, e.g. by preceding them with x'E0', but then also adding

Code:

ESCAPE x'E0'

after the LIKE.

Coming back to your original question:

Quote:

The following record exists in tbl01
key_txt = x'F0F2F1F8F0F0F0F6001A021302A80E2FC3C3F0F140404040 4040'
which has substr(key_txt,9,8) = x'001A021302A80E2F'

This indeed starts with the 8 EBCDIC characters '02180006', then 8 other bytes, then the EBCDIC characters 'CC01 ' (ending in 6 blanks).
BUT if the data is encoded with CCSID=935, x'0E' and x'0F' have a special meaning. One of those 8 bytes is the shift-out character (x'0E'), which means that at this point double-byte interpretation begins, i.e., x'2FC3' is one character, x'C3F0' is one character, etc. There is no shift-in (x'0F') to end double-byte interpretation.
If the data has no codepage ("FOR BIT DATA"), or a single-byte codepage, x'0E' is a "normal" byte and no interpretation is done. Apparently, in your situation, this is the case. So in any "WHERE key_txt = x'......'" also the right hand side is interpreted as "FOR BIT DATA", which explains why you find your row.

Code:

select * from tbl01
where key_txt like x'F0F2F1F86D6D6D6D001A021302A80E2FC3C3F0F1404040404040'

Here, the LIKE forces *textual* interpretation, since at BIND time the characters (not bytes) "%" and "_" (i.e., in codepage 935, these are x'6C' and x'6D') must be recognised.
For a key_txt without the byte x'0E' this works: the four x'6D' are four wildchars. But with the x'0E', double-byte interpretation starts, i.e., the byte x'0E' in key_txt is compared with the double-byte x'2FC3', and these are different.
Try the following, and your row should be found:

Code:

select * from tbl01
where key_txt like x'F0F2F1F86D6D6D6D001A021302A8D62FC3C3F0F1404040404040'

i.e., replace any occurrence of x'0E' or x'0F' by the wildcard x'6D'. (Which of course is a bit too permissive, so maybe some additional rows would be returned, which you can filter out by adding the condition AND substr(key_txt, 14,1) = x'0E').
When your search string has an x'6D' or x'6E', precede it in the LIKE argument with x'E0' and add "ESCAPE x'E0'" at the end:

Code:

select * from tbl01
where key_txt like x'F0F2F1F86D6D6D6D001A021302A8D62FC3C3F0F14040404040E0D6'
ESCAPE x'E0'

would search for a "_" as the last character.
Replace x'E0' by anything else when your search pattern would already have an x'E0'.

Internet Services

Web Development

Digital Marketing

Consumer Tech