Handle Hex Values in DB2 SQL
Abhishek Verma
Mainframe Modernization Go-to-market Leader Professional Services EMEA | Enterprise Transformation & Cloud Consulting
If you have any data which is unicode or contains special characters like German Umlauts (? ? ü) or any special Character like currency signs ￡, €, $ then you can use the hex values in SQL to fetch/update/insert the correct data from DB2.
Often in DB2 in Z/os we have this encoding or special character issue where our Database can handle special characters due to UTF-8 and UTF-16 support but not the SQL's inline in JCL or in PDS can handle. Sometime we see the special character gets corrupt and show incorrect results. So the best way to handle such special characters is by using hex value.
Steps to generate the Hex Values
1) For this you can use the notepad++ plugin
Select the data you want to convert into hex and click on Plugins >> Converter >> ASCII to Hex
2) Now Select the data and click on ASCII à HEX and it will convert the data in
3) Now use the Hex Values in SQL embedded in single quotes and precedence with X. Like for the hex value C3BC, we have to use X'C3BC' in SQL to refer german umlat ü
INSERT INTO SCHEMA.TABLE COL1 VALUES(X'C3BC')
4) To refer the sam hex value in LIKE clause in SQL refer the following sample query.
SELECT * FROM SCHEMA.TABLE WHERE COL1 LIKE '%' || X'C3BC' ||'%'