db2 special character 0x1A

问题:

How can I seach for a special character like "→" (0x1A)? An example for my query is:

select * from Data where Name like '%→%'

I want to use instead of "→" something like 0x1A.

I can't use any Java or C# Code. I just have SQuirrel to connect and send commands to the database.

回答1:

You can use chr() to search the charater:

select * from data where name like '%' + chr(26) + '%'

回答2:

Old topic, but a current issue nevertheless. My DB2 environment runs on AS400/iSeries and use EBCDIC instead of ASCII. I worked out the character from the EBCDIC table at lookuptables.com.

ASCII 0x1a translates to SUB, and SUB translates to EBCDIC 0x3f.

Using that in SQL looks like this:

select productDescription
from productsTable
where productDescription like '%' || x'3F' || '%'`

回答3:

Solution 1: Use chr(26).

Solution 2: Write the query in Java or another programming language which lets you build the SQL from pieces and where you can enter hex codes.

回答4:

This query has worked for me in the past on iSeries DB2.

select * from db/table where posstr(field, x'3F') > 0 

Trouble is you have to be certain of the hex value you are searching for in the string. I had a similar situation where the I was sure the hex code for the character was x'3F, but when I sub-string the non-viewable character it was actually x'22. You might want to single out the character that is giving you the issue and see what it's value is.

select hex(substr(field, 21,1)) from db/table where posstr(field, 'StringBeforeCharacter') > 0