oracle 字符串操作

1、替换

TRANSLATE(string,from_str,to_str)

返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。

简单说就是，从左到右一一对应的关系，如果不能对应，则视为空值。

字符一一对应的情况
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
TRANSLATE (
--------------
123456ghij

字符不一一对应的情况
SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;
TRANSL
----------
123456

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）

select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

因此：结果依次为：@#c##@@def 和@#c##@@ef

示例一：将数字转换为9，其他的大写字母转换为X，然后返回。

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') 
FROM DUAL

示例二：将数字保留，将其他的大写字母移除。

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

示例三：示例证明是按照字符来处理，不是按照字节来处理，如果to_string的字符数比from_string多的话，多出的字符数似乎没有什么用处，也不会引发异常。

SELECT TRANSLATE('我是中国人,我爱中国', '中国', 'China') "Translate example" FROM DUAL；
-------------
我是Chin人,我爱Chin

一个汉字 = 2个字节

示例四：下面的示例证明，如果from_string的字符数大于to_string，那么多出的字符会被移除，也就是ina三个字符会从char参数中移除，当然区分大小写啦。

SELECT TRANSLATE('I am Chinese, I love China', 'China', '中国') "Translate example" FROM DUAL;
---------------
I m 中国ese, I love 中国

示例五：以下示例证明，如果第二个参数为空字符串，整个返回null。

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

示例六：在银行转帐时经常看见账户人只显示姓名的最后一个字，其余的用星号代替，我就用translate来做个类似的东西吧。

SELECT TRANSLATE('韩小明',substr('韩小明',1,length('韩小明') - 2), rpad('*',length('韩小明'),'*')) "name"

FROM DUAL
---------------
****明

但是名字是叠字的不行，如韩梅梅等。会返回******

可以使用replace函数

SELECT replace('韩梅梅',substr('韩梅梅',1,length('韩梅梅') - 2), rpad('*',length('韩梅梅'),'*')) "name"

FROM DUAL
---------------
****梅

REPLACE ( char, search_string [, replace_string])

如果没有指定replace_string 变量的值，那么当发现search_string 变量的值时，就将其删除。

select replace('0123456789','0','a') from dual;--a123456789

select replace('0123456789','0','') from dual;--123456789

select replace('0123456789','0') from dual;--123456789

补充：　　
rpad(string,padded_length,[pad_string]) 　　

从右边对字符串使用指定的字符进行填充
string 表示：被填充的字符串 　　
padded_length 表示：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符; 　　
pad_string 是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。 　　

例如： 　　
rpad('tech', 7); 将返回'tech ' 　　
rpad('tech', 2); 将返回'te' 　　
rpad('tech', 8, '0'); 将返回'tech0000' 　　
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net' 　　
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'

2、截取

Instr()和substr()

instr函数对某个字符串进行判断，判断其是否含有指定的字符。返回字符的位置。

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

从一个字符串中查找指定子串的位置。例如：
SQL> select instr('yuechaotianyuechao','ao') position from dual;
 
 POSITION
 ----------
 6
 
从第7个字符开始搜索
SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;
 
 POSITION
 ----------
 17
 
从第1个字符开始，搜索第2次出现子串的位置
SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;
 
 POSITION
 ----------
 17

substr函数的用法，取得字符串中指定起始位置和长度的字符串  ，默认是从起始位置到结束的子串。

substr('目标字符串',开始位置,长度)

substr('This is a test', 6, 2)     would return 'is'
截取点号之前的字符串
SUBSTR ('C3411.907w15',  0,  INSTR ('C3411.907w15, '.', 1, 1) - 1) 

截取点号之后的字符串
SUBSTR ('C3411.907w15', INSTR ('C3411.907w15', '.', 1, 1)+1)

TRUNC

TRUNC()函数处理number型数字，只截取，不进行四舍五入

    语法格式：TRUNC（number[,decimals]）

    其中： number 待做截取处理的数值；decimals 指明需保留小数点后面的位数，可选项，忽略它则截去所有的小数部分。

    示例：

    select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
    select trunc(123.567,-2) from dual;--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
    select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

使用正则

REGEXP_LIKE：(匹配) 比较一个字符串是否与正则表达式匹配  

    (srcstr, pattern [, match_option])  

REGEXP_INSTR：(包含)在字符串中查找正则表达式，并且返回匹配的位置  

    (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])  

REGEXP_SUBSTR：(提取) 返回与正则表达式匹配的子字符串  

    (srcstr, pattern [, position [, occurrence [, match_option]]])   

REGEXP_REPLACE：(替换)搜索并且替换匹配的正则表达式  

    (srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])  

如：从字符串中提取数值

select regexp_replace('ORACLE_3035.2','[^0-9\.]','') from dual;  --> 返回：3035.2

[^0-9\.]'即除了0-9与小数点之外，其余被替换为空。

---

srcstr:        被查找的字符数据。   

pattern:       正则表达式。  

occurrence:    出现的次数。默认为1。  

position:      开始位置  

return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。  

replacestr:    用来替换匹配模式的字符串。  

match_option:  匹配方式选项。缺省为c。

• i：大小写不敏感；
• c：大小写敏感；（默认）
• n：不匹配换行符号；
• m：多行模式；
• x：扩展模式，忽略正则表达式中的空白字符。

###########################################################################

边界符集合

^   每一行的开头，单行模式下等价于字符串的开头

$   每一行的结尾，单行模式下等价于字符串的结尾

在单行模式下，^和\A等价，$和\Z等价。

##########################################################################

重复次数集合

*   匹配零次或多次--尽可能多的次数

?   零次或一次

+   一次或多次

{m} {m}?正好m次，贪婪与非贪婪一样的

{m,}    {m,}?至少m次

{m, n}  {m, n}?最少m最多n次

在上述字符后加？，如：*?，即为非贪婪模式。

贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符

如最短匹配：

>>> import re
>>> str = '<a>this is first label</a><a>the second label</a>'

>>> print re.findall(r'<a>(.*?)</a>', str)  # 最短匹配
['this is first label', 'the second label']

>>> print re.findall(r'<a>(.*)</a>', str)
['this is first label</a><a>the second label']

###############################################################

组合操作符

[…]     方括号内任意字符或字符集合中的一个。在其中所有的操作符号都被视为普通符号，但以下除外：

★范围操作符：- 但当"-"位于字符列表的第一个或最后一个字符时，或位于范围的结尾时，会被当作"-"本身来看待。

当右方括号"]"出现在列表的第一个位置时，它会被当作"]"本身来处理。

[^…]    方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符

(…)     圆括号，将复杂表达式当作单一表达式来处理

..|..    或

abc      和。直接将字符连在一起写

优先级比较：圆括号>重复次数操作符>和>或。

例如，(f|ht)tps?: 表示

ftp:

ftps:

http:

https:

######################################################################

匹配操作符

\n      即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右。

转义操作符

\       将其后紧跟着的操作字符当作普通字符看待。

例如 abc*def 可以匹配 abdef或abcccdef等，但无法匹配abc*def，后者需要abc\*def才能匹配

##################################################

一条正则表达式匹配函数示例解析：

SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' ) 

这个语句是从表中查手机号。

^ 表示开始

$ 表示结束 

[]内部为匹配范围

{}里的内容表时个数

手机号码的特点是以 1开头接着是3或5再加9位的数字 所以这么理解

1开头 表达式为 ^[1]{1} 意为 开始1位里包含1

3或5 表达式为 [35]{1}，即第二位包含有3或5的

9位数字结束 为: [[:digit:]]{9}$ 这里[:digit:]为特殊写法，代表为数字，再加个结束符$。即最后9位是数字。