Igor Nikiforov has contributed various string manipulation functions for DB2 using SQL PL. You may see this link for the original article.
You may download the script from this link.
The following string manipulation functions are included in above script.
- AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by AT() is case-sensitive. AT is similar to the Oracle function INSTR.
- RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RAT() is case-sensitive.
- ATC(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (including overlaps). The search performed by ATC() is case-insensitive. ATC similar to the Oracle function INSTR.
- RATC(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string (including overlaps). The search performed by RATC() is case-insensitive.
- AT2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by AT2() is case-sensitive. AT2 similar to the Oracle function INSTR.
- ATC2(): Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character (excluding overlaps). The search performed by ATC2() is case-insensitive. ATC similar to the Oracle function INSTR.
- OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
- OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). OCCURS2 is faster than OCCURS.
- PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADL similar to the Oracle function LPAD.
- PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADR similar to the Oracle function RPAD.
- PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
- CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. CHRTRAN similar to the Oracle function TRANSLATE.
- STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
- STRFILTER(): Removes all characters from a string except those specified.
- GETWORDCOUNT(): Counts the words in a string.
- GETWORDNUM(): Returns a specified word from a string.
- GETALLWORDS(): Inserts the words from a string into the table.
- PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. PROPER similar to the Oracle function INITCAP.
- RCHARINDEX(): Similar to the SQL Procedural Language DB2 function LOCATE, with a Right search.
- ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
- ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).