Oracle Translate & Replace

发布时间：2020-12-12 15:06:48 所属栏目：百科来源：网络整理

导读：点击打开链接 Oracle Translate Replace Version 11.1 Note: Translate and replace are very similar in their appearance but can produce very different results. Translate replaces by position,the first character of the list to match is replaced

Oracle Translate & ReplaceVersion 11.1Note:Translate and replace are very similar in their appearance but can produce very different results. Translate replaces by position,the first character of the list to match is replaced by the first character of the replacement list. The second character with the second,and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped.

Replace replaces the string to match with the replacement string. The replacement of a single character is the same as that ofTRANSLATE.SyntaxTRANSLATE(
str1VARCHAR2CHARACTER SET ANY_CS,
srcVARCHAR2CHARACTER SET STR1%CHARSET,
destVARCHAR2CHARACTER SET STR1%CHARSET)
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;Translate Built-in String FunctionSingle Character
ReplacementTRANSLATE(<string>,<'list_to_match'>,<'replacements_list'>)

This demo replaces all commas with vertical bars.SELECTTRANSLATE('comma,delimited,list',',','|')
FROMDUAL;Multiple Character
ReplacementThe following takes a DNA sequence and returns its complementSELECTTRANSLATE('CAG-TTT-GAC-ACA-TGG-ATC','ACGT','GATC') DNA
FROMDUAL;Character Replacement
And EliminationThe a is replaced with an e,the h has no complement and is dropped.SELECTTRANSLATE('So What','ah','e')
FROMDUAL;Eliminating Double
QuotesCapital A is replaced with capital A. The double quote is eliminated because there is no match.SELECTTRANSLATE('"Darn double quotes"','A"','A')
FROMDUAL;Encryption / DecryptionIn this demo a string is first encrypted then decryptedSELECTTRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz','0123456789qwertyuiop[kjhbv')
FROMDUAL;

SELECTTRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv','abcdefghijklmnopqrstuvxyz')
FROMDUAL;Counting VowelsIn this demo the number of vowels in the string is countedWITH dataAS(SELECT'Whose line is it anyway' lineFROMDUAL)
SELECTLENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) nbVowels
FROMdata;Replace Built-in String FunctionREPLACE (overload 1)REPLACE(
srcstrVARCHAR2CHARACTER SET ANY_CS,
oldsubVARCHAR2CHARACTER SET SRCSTR%CHARSET,
newsubVARCHAR2CHARACTER SET SRCSTR%CHARSET := NULL)
RETURNVARCHAR2CHARACTER SET SRCSTR%CHARSET;REPLACE (overload 2)REPLACE(
srcstrCLOBCHARACTER SET ANY_CS,
oldsubCLOBCHARACTER SET SRCSTR%CHARSET,
newsubCLOBCHARACTER SET SRCSTR%CHARSET := NULL)
RETURNCLOBCHARACTER SET SRCSTR%CHARSET;Single Character
ReplacementREPLACE(<string>,<'string_to_match'>,<'replacements_string'>)SELECTREPLACE('So What','o','ay')
FROMDUAL;Multiple Character
ReplacementReplacement of a single character with a phraseSELECTREPLACE('An ideathat is not dangerous is unworthy of being called an ideaat all.','n idea','software program') TRUTH
FROMDUAL;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

点击打开链接

Oracle Translate &amp; Replace

Oracle Translate & Replace