One 、 grammar ： 
TRANSLATE(string,from_str,to_str)

Two 、 Purpose  
Return to （ All that appears ）from_str Replace each character in with to_str After the corresponding character in string.TRANSLATE yes REPLACE A superset of the capabilities provided . If from_str Than to_str Long , So in from_str Not in to_str The extra characters in will be removed from string The deleted , Because they don't have corresponding replacement characters .to_str Can't be empty .Oracle Interpret an empty string as NULL, And if the TRANSLATE Any parameter in is NULL, So the result is NULL.

3、 ... and 、 Allowed locations  
Procedural statements and SQL sentence .

Four 、 Example  
Sql Code

1. SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; 
2. TRANSLATE ( 
3. -------------- 
4. 123456ghij 
5. 
6. SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual; 
7. TRANSL 
8. ---------- 
9. 123456

grammar ：TRANSLATE(expr,from,to)

expr: Represents a string of characters ,from And to It's a one-to-one correspondence from left to right , If it doesn't correspond to , Then it is regarded as a null value .

give an example ：

select translate('abcbbaadef','ba','#@') from dual （b Will be ＃ replace ,a Will be ＠ replace ）

select translate('abcbbaadef','bad','#@') from dual （b Will be ＃ replace ,a Will be ＠ replace ,d The corresponding value is null , Will be removed ）

therefore ： The results were as follows ：@#c##@@def and @#c##@@ef

grammar ：TRANSLATE(expr,from,to)

expr: Represents a string of characters ,from And to It's a one-to-one correspondence from left to right , If it doesn't correspond to , Then it is regarded as a null value .

give an example ：

select translate('abcbbaadef','ba','#@') from dual （b Will be ＃ replace ,a Will be ＠ replace ）

select translate('abcbbaadef','bad','#@') from dual （b Will be ＃ replace ,a Will be ＠ replace ,d The corresponding value is null , Will be removed ）

therefore ： The results were as follows ：@#c##@@def and @#c##@@ef

Examples are as follows ：

Example 1 ： Convert numbers to 9, The other capital letters are converted to X, Then return .

SELECT TRANSLATE('2KRW229',

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "ic"

FROM DUAL

Example 2 ： Keep the numbers , Remove other capital letters .

SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example" FROM DUAL

Example 3 ： The example shows that it is handled according to characters , Not in bytes , If to_string The number of characters is more than from_string Many words , The extra number of characters doesn't seem to be useful , It doesn't throw an exception .

SELECT TRANSLATE(' I'm Chinese , I love China ', ' China ', 'China') "Translate

example"

FROM DUAL

Example 4 ： The following example demonstrates , If from_string Is greater than to_string, So many characters will be removed , That is to say ina Three characters from char Remove... From parameters , Case sensitive, of course .

SELECT TRANSLATE('I am Chinese, I love China', 'China', ' China ') "Translate

example"

FROM DUAL

Example 5 ： The following example demonstrates , If the second parameter is an empty string , The whole return null.

SELECT TRANSLATE('2KRW229',              '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '') "ic"    FROM DUAL

Example 6 ： The last word , The rest of the asterisks replace :

SELECT TRANSLATE(' I'm in Shanghai ',substr(' I'm in Shanghai ',1,length(' I'm in Shanghai ') - 1),rpad('*',length(' I'm in Shanghai '),'*')) "ic"FROM DUAL;

【 function 】oracle translate() Detailed explanation + Examples of more related articles

  1. Oracle Data dictionary details

    Learning notes :oracle Data dictionary details --- This paper is about TTT Learning notes , First, introduce the data dictionary and how to view it , Then classify and summarize the tables and views of various data dictionaries . Then list some by laws .   Data dictionary system table , Save in system Tablespace . ...

  2. 【Oracle】 Detailed explanation ORACLE Medium trigger( trigger )

    The main content of this article is as follows : 8.1 Trigger Type 8.1.1 DML trigger 8.1.2 Alternative triggers 8.1.3 System triggers 8.2  Create trigger 8.2.1 Trigger order 8.2.2 establish DML trigger 8.2. ...

  3. We often use classics SQL Sentence complete edition -- Detailed explanation + example ( save )

    We often use classics SQL Sentence complete edition -- Detailed explanation + example turn   Silly bean's blog http://blog.sina.com.cn/shadou2012  http://blog.sina.com.cn/s/blog_84 ...

  4. problem ：Oracle The starter ; result :1、Oracle Trigger details ,2、Oracle Trigger example

    ORACLE Trigger details The main content of this article is as follows : 8.1 Trigger Type 8.1.1 DML trigger 8.1.2 Alternative triggers 8.1.3 System triggers 8.2  Create trigger 8.2.1 Trigger order 8.2.2 gen ...

  5. We often use classics SQL Sentence complete edition -- Detailed explanation + example 《 From the Internet , It's not organized , Deposit with this 》

    We often use classics SQL Sentence complete edition -- Detailed explanation + example Part of the following statement is Mssql sentence , Not in access Use in . SQL classification : DDL— Data definition language (CREATE,ALTER,DROP,DECLARE) DML ...

  6. oracle Data type details --- Date type （ Reprint ）

    oracle Data type details --- Date type oracle The data type looks very simple , But when you use it, you will find that there are many knowledge points , This article is about me ORACLE Some collation of date data types , It's all about developing introductory materials , Share with you : notes : because INTER ...

  7. oracle Sequence Detailed explanation

    Sequence : yes oacle The database object provided for generating a series of unique numbers . l  Automatically provide unique values l  Shared objects l   It is mainly used to provide primary key values l  Loading sequence values into memory can improve access efficiency Create sequence : 1.  You have to create ...

  8. Message,MessageQueue,Looper,Handler Detailed explanation + example

    Message,MessageQueue,Looper,Handler Detailed explanation + example Original address Android Of Handler Use ( This is a brief introduction Handler Use ) One . Several key concepts 1.Message ...

  9. oracle checkpoint Detailed explanation

    Oracle checkpoint Detailed explanation topcheckpoint Eliminate illiteracy top What is? checkpoint In the database system , Write log and write data file are in the database IO The two most expensive operations , In these two operations, writing a data file belongs to ...

Random recommendation

  1. Oracle Study note 1 First time to know Oracle

    Database introduction database (Database) It's organized according to the data structure . Warehouse for storing and managing data .SQL yes Structured Query Language( Structured query language ) The acronym for . Definition database , Simple ...

  2. Some concepts related to cloud computing Baas、Saas、Iaas、Paas

    BaaS( Back end as a service :Backend as a Service) The company provides boundary services for mobile application developers to integrate cloud back ends . SaaS( Software as a service :Software as a Service) Provides a complete direct use ...

  3. See following ： Bypass WAF continue SQL Common methods of Injection

    Web Hacker Always live with WAF In constant struggle , Manufacturers keep filtering ,Hacker Keep bypassing .WAF bypass It's an eternal topic , Many basic friends have also summed up a lot of strange skills and tricks . I'll do a little literacy here today . First to speak ...

  4. JavaScript-- Format current time

    <!doctype html> <html> <head> <meta charset="UTF-8"> <title> ...

  5. One 、ASP.NET Routing route （ In depth analysis of routing system architecture principle ）

    Read the directory : 1. Introduction to 2.ASP.NET Routing The location of the routing object model 3.ASP.NET Routing Entry to the routing object model 4.ASP.NET Routing The internal structure of the routing object model 4 ...

  6. The pointer is very strong （ Remember the priority ：() &gt; [] &gt; *）

    Reference resources :http://blog.chinaunix.net/uid-20120277-id-5760985.html C Language all complex pointer declarations , It's all made up of various declaration nesting . How to interpret complex pointer statements ? Right left rule ...

  7. SpringBoot Project creation process （ preliminary ）

    In the process of learning, my friends probably found that there are many places for us to manually configure these two frameworks , Just do it JavaEE The developers must have heard of “ Convention over configuration ” In a word , That is to say, the system , Class library , The framework should assume reasonable default values , Instead of asking for ...

  8. About SQL\SQL Server Three valued logic of

    stay SQL When I started , We filter for a column with a value of NULL The line of , Generally, the following methods will be adopted : SELECT * FROM Table AS T WHERE T.Col=NULL In fact, this way of writing can not get the desired results . this ...

  9. bzoj3997

    Answer key : dp f[i][j]=max(f[i-1][j+1]+a[i][j],max(f[i-1][j],f[i][j+1])); Code : #include<bits/stdc++.h> ...

  10. GreenDao- Customize SQL Inquire about -AndroidStudio

    /** * function : Employee inquiries * Method parameter : * strEmpIdOrEmpName: staff ID perhaps Employee name * strQueryType: Employee query type "0": " staff ...