Oracle/PLSQL: Replace Function
--------------------------------------------------------------------------------
In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.
The syntax for the replace function is:
replace( string1, string_to_replace, [ replacement_string ] )
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.
Applies To:
•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
For example:
replace('123123tech', '123'); would return 'tech'
replace('123tech123', '123'); would return 'tech'
replace('222tech', '2', '3'); would return '333tech'
replace('0000123', '0'); would return '123'
replace('0000123', '0', ' '); would return ' 123'
www.techonthenet.com/oracle/functions/rtrim.php
Oracle/PLSQL: Rtrim Function
--------------------------------------------------------------------------------
In Oracle/PLSQL, the rtrim function removes all specified characters from the right-hand side of a string.
The syntax for the rtrim function is:
rtrim( string1, [ trim_string ] )
string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.
Applies To:
•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
For example:
rtrim('tech '); would return 'tech'
rtrim('tech ', ' '); would return 'tech'
rtrim('123000', '0'); would return '123'
rtrim('Tech123123', '123'); would return 'Tech'
rtrim('123Tech123', '123'); would return '123Tech'
rtrim('Techxyxzyyy', 'xyz'); would return 'Tech'
rtrim('Tech6372', '0123456789'); would return 'Tech'
The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
rtrim('Techxyxzyyy', 'xyz'); would return 'Tech'
It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example.
rtrim('Tech6372', '0123456789'); would return 'Tech'
In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.
Oracle/PLSQL: Substr Function
--------------------------------------------------------------------------------
In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.
The syntax for the substr function is:
substr( string, start_position, [ length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.
Applies To:
•Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
For example:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'
分享到：
相关推荐
Oracle PL/SQL 子程序和包的概念和应用 本章要点： * 理解子程序（过程、函数）及包的概念 * 掌握如何创建、执行和删除过程、函数及包的方法 * 了解形参和实参的区别以及不同参数模式的特征 * 灵活使用过程、函数...
CREATE OR REPLACE FUNCTION average (p_numbers NUMBER[]) RETURN NUMBER IS total NUMBER := 0; count NUMBER := 0; BEGIN FOR i IN 1..p_numbers.COUNT LOOP total := total + p_numbers(i); count := count...
创建函数的语法是`CREATE OR REPLACE FUNCTION`，并在函数体中使用`RETURN`语句指定返回值。 9. **包**：包是相关过程和函数的集合，可以封装私有变量和过程，提供更好的模块化和代码复用。 在实验过程中，你可能...
- 创建：`CREATE OR REPLACE FUNCTION func_name (p1 IN datatype) RETURN datatype IS BEGIN ... END;` - 调用：`SELECT func_name(value) FROM DUAL;` #### 七、数据操作和控制语言 - **DML**: 数据操纵语言，...
CREATE OR REPLACE FUNCTION func_name (param1 IN NUMBER) RETURN NUMBER AS result NUMBER; BEGIN result := param1 * 3; RETURN result; END; ``` 6. **触发器**：触发器是在特定数据库事件（如INSERT、...
Oracle P/L SQL实现文件压缩、解压功能，以下是此过程包的头部，包体经常打包处理plb，感兴趣用户可以下载下来。 Create or Replace Package UTL_ZIP AUTHID CURRENT_USER as Type File_List is Table of Clob; -...
### ORACLE_PlSql-甲骨文学习笔记 #### 一、创建表 ##### 创建表一 ```sql CREATE TABLE TABLE_NAME ( AAA INTEGER CONSTRAINT PK_TABLE_NAME PRIMARY KEY, BBB VARCHAR2(10) NOT NULL, DOB DATE, CCC VARCHAR...
CREATE OR REPLACE FUNCTION get_maxsal_dept(v_deptno NUMBER) RETURN NUMBER IS v_max_sal NUMBER(10, 2); -- 定义变量存储最大薪资 BEGIN SELECT MAX(e.sal) INTO v_max_sal -- 查询最大薪资 FROM SCOTT.EMP e...
* 创建存储函数的基本语法：create function 函数名 (参数列表) return 数据类型 is [as] begin 执行部分 end; * 过程和函数的调用：exec 过程名 (参数值 1, …) 或 call 过程名 (参数值 1, …)。 三、PL/SQL 块...
两者都可以通过`CREATE [OR REPLACE] PROCEDURE`和`CREATE [OR REPLACE] FUNCTION`语句来创建。过程的定义结构通常包含`AS`或`IS`关键字后的声明部分、可执行部分以及异常处理部分。函数的定义也类似，只是需要指定...
create or replace function f(x in Pkg.t) return varchar2 authid definer is r varchar2(80); begin for j in 1..x.count loop r := r || 'n=' || x(j).n || ' v=' || x(j).v; end loop; return r; end f; `...
CREATE OR REPLACE FUNCTION function_name (parameter_list) RETURN data_type IS -- Variable declarations BEGIN -- Function body RETURN expression; END; ``` - **调用函数**：在SQL查询或PL/SQL代码...
CREATE OR REPLACE FUNCTION func_name (param1 datatype1) RETURN datatype2 IS BEGIN -- 语句 RETURN result; END; ``` 7. **包**：包是存储过程和函数的容器，可以包含公共变量和常量，提供模块化和封装。...
CREATE OR REPLACE FUNCTION add_numbers(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN result := a + b; RETURN result; END; ``` 5. **包（Packages）**：包是存储在数据库中的命名...
Create or Replace Package UTL_FTP AUTHID CURRENT_USER as Type Connection is Record( Connection UTL_TCP.Connection, AccountInfo VarChar2(1000), TransferMethod Char(1), --A: ASCII...
在"Oracle PLSQL基本语法"中，我们可以探讨以下几个关键知识点： 1. **变量声明**： 在PL/SQL中，我们需要先声明变量，指定其数据类型。例如： ```sql DECLARE var1 NUMBER; var2 VARCHAR2(20); BEGIN -- ...
SQL>select text from all_source where owner=user and name=upper('&plsql_name'); 三、查看数据库的SQL 1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size ...