Tags

, , , , , , , , ,

Many a times in the input feed that you receive, you will have characters with accents, which will be loaded in your table as-is. For ease of use, you would like to remove the accents when performing comparisons or while sending it to other systems.

You can make use of REPLACE function and the HEX value of the character with accent to replace it with what you need.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
PWN_PART_AKA_NME,
X’AB’,’?’),X’62’,’A’),X’63’,’A’),X’64’,’A’),
X’65’,’A’),X’66’,’A’),X’67’,’A’),X’71’,’E’),
X’72’,’E’),X’73’,’E’),X’74’,’E’),X’75’,’I’),
X’76’,’I’),X’77’,’I’),X’78’,’I’),X’69’,’N’),
X’EB’,’O’),X’EC’,’O’),X’ED’,’O’),X’EE’,’O’),
X’EF’,’O’),X’FB’,’U’),X’FC’,’U’),X’FD’,’U’),
X’FE’,’U’),X’BA’,’Y’),X’68’,’C’)

The above statement removes the accents from the characters and gives you the name with characters without accents.

¿ (changes to ?)
Â,Ä,À,Á,Ã,Å (changes to A)
É,Ê,Ë,È (changes to E)
Í,Î,Ï,Ì (changes to I)
Ñ (changes to N)
Ô,Ö,Ò,Ó,Õ (changes to O)
Û,Ü,Ù,Ú (changes to U)
Ý (changes to Y)
Ç (changes to C)

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)
Advertisements