-- ASCII( single_character )
SELECT ASCII('t') FROM DUAL;
Result: 116
-- CHR( number_code )
SELECT CHR(116) FROM DUAL;
Result: 't'
-- ASCIISTR( string )
-- ASCII version of the string in the database character set. Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.
SELECT ASCIISTR('ABÄCDE') FROM DUAL;
Result: AB\00C4CDE
-- UNISTR
-- UNISTR takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set. The national character set of the database can be either AL16UTF16 or UTF8.
SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;
Result: abcåñö
-- DUMP
-- DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.
-- DUMP( expression [, return_format] [, start_position] [, length] )
SELECT DUMP('Tech'),DUMP('Tech', 10),DUMP('Tech', 16),DUMP('Tech', 1016),DUMP('Tech', 1017) FROM DUAL;
Result: Typ=96 Len=4: 84,101,99,104 Typ=96 Len=4: 84,101,99,104 Typ=96 Len=4: 54,65,63,68 Typ=96 Len=4 CharacterSet=AL32UTF8: 54,65,63,68 Typ=96 Len=4 CharacterSet=AL32UTF8: T,e,c,h
-- REPLACE( string1, string_to_replace [, replacement_string] )
-- replace unicode 00A0 character example
SELECT REPLACE(<COLUMN_NAME>, unistr('\00A0'),'') FROM DUAL;
-- REGEXP_REPLACE( string, pattern [, replacement_string [, start_position[,nth_appearance [, match_parameter ] ] ] ] )
-- replace nonprintable characters except Enter “chr(13)”
REGEXP_REPLACE(txt,'['||chr(1)||'-'||chr(9)||']|['||chr(11)||'-'||chr(12)||']|['||chr(14)||'-'||chr(31)||']','') -->
-- Replace Function for Non Prinatable Characters From Clob Column
CREATE OR REPLACE FUNCTION SCHEMA.NONPRINTABLE (txt CLOB) RETURN CLOB DETERMINISTIC IS
v_txt clob;
BEGIN
select regexp_replace(txt,'['||chr(1)||'-'||chr(9)||']|['||chr(11)||'-'||chr(12)||']|['||chr(14)||'-'||chr(31)||']',' ') into v_txt from dual;
RETURN v_txt;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END;
/
Etiketler :
Oracle