Translate Built-in String Function | |
Single Character Replacement | TRANSLATE(<string>, <'list_to_match'>,<'replacements_list'>) This demo replaces all commas with vertical bars. |
SELECT TRANSLATE('comma,delimited,list', ',', '|') FROM dual; | |
Multiple Character Replacement | The following takes a DNA sequence and returns its complement |
SELECT TRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC', 'ACGT', 'GATC') DNA FROM dual; | |
Character Replacement And Elimination | The a is replaced with an e, the h has no complement and is dropped. |
SELECT TRANSLATE('So What', 'ah', 'e') FROM dual; | |
Eliminating Double Quotes | Capital A is replaced with capital A. The double quote is eliminated because there is no match. |
SELECT TRANSLATE('"Darn double quotes "', 'A"', 'A') FROM dual; | |
Encryption / Decryption | In this demo a string is first encrypted then decrypted |
SELECT TRANSLATE('this is a secret', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv') FROM dual; SELECT TRANSLATE('p78o 8o 0 o42i4p', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz') FROM dual; | |
Counting Vowels | In this demo the number of vowels in the string is counted |
WITH data AS (SELECT 'Whose line is it anyway' line FROM dual) SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) nbVowels FROM data; |
translate function
最新推荐文章于 2024-05-28 15:49:17 发布