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
- 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 . ...
- 【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. ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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
- 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 ...
- 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 ...
- 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 ...
- JavaScript-- Format current time
<!doctype html> <html> <head> <meta charset="UTF-8"> <title> ...
- 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 ...
- The pointer is very strong ( Remember the priority :() > [] > *)
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 ...
- 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 ...
- 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 ...
- 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> ...
- GreenDao- Customize SQL Inquire about -AndroidStudio
/** * function : Employee inquiries * Method parameter : * strEmpIdOrEmpName: staff ID perhaps Employee name * strQueryType: Employee query type "0": " staff ...