How to get ASCII/EBCDIC character in IBM db2 iSeries

db2ibm-midrange

I want to get character quote(') and double quote(") ASCII/EBCDIC, in IBM DB2 iSeries.
Honestly i don't know what exactly iSeries supports – ASCII or EBCDIC. I'm trying both, but no luck.

http://www.simotime.com/asc2ebc1.htm

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm

Example: get (A) character

SELECT * FROM MYTABLE WHERE CUSTOMER_NAME LIKE '%' + CHR(065) + '%'

SELECT * FROM MYTABLE WHERE CUSTOMER_NAME LIKE '%' + CHR(41) + '%'

SELECT * FROM MYTABLE WHERE CUSTOMER_NAME LIKE '%' + CHR(193) + '%'

Gives me this error:

SQL0204 – CHR in *LIBL type *N not found.

Best Answer

If you are simply trying to put a single quote (apostrophe) in your string delimited by single quotes, you need to double it, like this '%''%'. If you want to concatenate, use || (in unix, known as 2 "pipe" characters), not + (plus). You didn't need hex in your example, but if you had, the way to do it would have been x'C1' (an 'A' in EBCDIC).

iSeries uses EBCDIC natively for most things in general. There is a char() function.

Your link to online documentation is for DB2 on Linux, Unix, and Windows. You want the iSeries Information Center at http://publib.boulder.ibm.com/eserver/ibmi.html , or more specifically (depending on which OS release you are using)

Related Topic