1

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.

Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
Stefan
  • 21
  • 2
  • 4

4 Answers4

2

You can use chr() to search the charater:

select * from data where name like '%' + chr(26) + '%'
Wadih M.
  • 12,810
  • 7
  • 47
  • 57
1

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' || '%'`
Simon Fredriksson
  • 77
  • 1
  • 2
  • 8
0

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.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
0

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
jwbradley
  • 11
  • 3