名称 | 格式 | 功能 | 实例 |
Ascii | ascii(single_character ) | 返回指定字符的ASCII码 | ascii('T') return 84. ascii('T2') return 84. |
asciistr | asciistr( string ) | 讲字符转换为数据库可是别的ASCII字符 | asciistr('A B C Õ Ø') return 'A B C /00D5 /00D8' |
convert | convert( string1 , char_set_to , [ char_set_from ] ) char_set_to:新的字符集 char_set_from:原字符集 | 从一个字符集转换为另一个字符集 | convert('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') return 'A B C D E A E I ? ?' |
lower | lower( string1 ) | 转换为小写 | lower('Tech on the Net'); return 'tech on the net' |
upper | upper( string1 ) | 转换为大写 | upper('Tech on the Net'); return 'TECH ON THE NET' upper('george burns 123 '); return 'GEORGE BURNS 123 ' |
soundex | soundex( string1 ) | 返回拼音代码字符 | soundex('TECH ON THE NET'); return 'T253' soundex('apples'); return 'A142' |
chr | chr ( number_code ) | 将ASCII码转换为字符 | chr(116); would return 't'. chr(84); would return 'T'. |
compose | compose( string ) | 返回Unicode字符串 | compose('o' || unistr('/0308') ) return ö compose('a' || unistr('/0302') ) return â |
decompose | decompose( string ) | 返回Unicode字符串 | decompose('é') would return 'e´' decompose('olé') would return 'ole´' |
Concat (||) | concat( string1, string2 ) | 将连个字符传连接为一个字符串 | concat('Tech on', ' the Net'); return 'Tech on the Net'. concat('a', 'b') return 'ab'. |
dump | dump( expression, [return_format], [start_position], [length] ) | 返回一个varchar2值,其中包括数据类型代码,以字节为单位的长度和标准表达。 | dump('Tech') return 'Typ=96 Len=4: 84,101,99,104' dump('Tech', 10) return 'Typ=96 Len=4: 84,101,99,104' dump('Tech', 16)return 'Typ=96 Len=4: 54,65,63,68' |
instr | instr( string1, string2 [, start_position [, nth_appearance ] ] ) string1:原字符串 string2:需要查找的子字符串 start_position:查找开始的位置 nth_appearance:第几个子字串 | 在字符串中查找子字串所在的位置。start_position<0标示从右开始查找。 | instr('Tech on the net', 'e') return 2; the first occurrence of 'e' instr('Tech on the net', 'e', 1, 1) return 2; the first occurrence of 'e' instr('Tech on the net', 'e', -3, 2) return 2. |
length | length( string1 ) | 获得字符串长度 | length('') would return NULL. length('Tech on the Net') would return 15. |
lpad | lpad( string1, padded_length, [ pad_string ] ) string1:等待截取的字符串 padded_length,:截取的长度 pad_string:添补的字符 | 从左边进行字符串截取,如果截取的长度大于字符串的长度则由指定的填补字符进行填补,如果没有指定填补字符串则用空格在左边填补 | lpad('tech', 2); return 'te' lpad('tech', 8, '0'); return '0000tech' lpad('tech on the net', 15, 'z'); return 'tech on the net' lpad('tech on the net', 16, 'z'); return 'ztech on the net' |
rpad | rpad( string1, padded_length, [ pad_string ] ),参数参看lpad | 从右边进行字符传截取。 | rpad('tech', 7); return 'tech ' rpad('tech', 8, '0'); return 'tech0000' rpad('tech on the net', 15, 'z'); return 'tech on the net' rpad('tech on the net', 16, 'z'); return 'tech on the netz' |
ltrim | ltrim( string1, [ trim_string ] ) trim_string:需要去除的字符 | 去除字符串左边的指定字符,如果不指定字符则默认去除左边空格 | ltrim(' tech'); return 'tech' ltrim('123123Tech123', '123'); return 'Tech123' ltrim('xyxzyyyTech', 'xyz'); return 'Tech' |
rtrim | rtrim( string1, [ trim_string ] ) 参数参看ltrim | 去掉字符串右边指定的字符,,如果不指定字符则默认去除右边空格 | rtrim('tech '); return 'tech' rtrim('Tech123123', '123'); return 'Tech' rtrim('Techxyxzyyy', 'xyz'); return 'Tech' |
replace | replace( string1, string_to_replace, [ replacement_string ] ) string_to_replace:需要替换的字符 replacement_string: 新字符 | 字符替换,如果不指定新字符,则将要替换的字符删除掉,并不是替换为空字符。 | replace('123tech123', '123'); return 'tech' replace('222tech', '2', '3'); return '333tech' replace('0000123', '0', ' '); return ' 123' |
substr | substr( string, start_position, [ length ] ) start_position:截取的开始位置 length:截取的长度 | 字符传截取。start_position < 0表示从右边开始截取。但截取的长度是从左往右数。首字符位置为1。 | substr('This is a test', 6, 2) return 'is' substr('This is a test', 6) return 'is a test' substr('TechOnTheNet', 1, 4) return 'Tech' substr('TechOnTheNet', -3, 3) return 'Net' substr('TechOnTheNet', -6, 3) return 'The' |
translate | translate( string1, string_to_replace, replacement_string ) string_to_replace:需要替换的字符 replacement_string:新字符 | 翻译性替换,在原始字符串中查找需要替换的字符,然后用新字符进行替换(需要替换的字符并不一定能够是连续的) | translate('1tech23', '123', '456); return '4tech56' translate('222tech, '2ec', '3it'); return '333tith' |
trim | trim( [ leading | trailing | both [ trim_character ] ] string1 ) leading: 去除左边指定字符 trailing:去除右边指定字符 both :去除两边指定字符 | 去除字符串两边指定的字符,如果没有指定字符则去除两边的空格 | trim(' tech ') return 'tech' trim(' ' from ' tech ') return 'tech' trim(leading '0' from '000123') return '123' trim(trailing '1' from 'Tech1') return 'Tech' trim(both '1' from '123Tech111') return '23Tech' |
vsize | vsize( expression ) | 返回字符串所占的 bytes 数 | vsize('Tech on the net') return 15 vsize('Tech on the net ') return 16 vsize(null) return <null> vsize(' ') return 1 |