Oracle中字符操作函数

来源：互联网发布：番茄表单怎么导出数据编辑：程序博客网时间：2024/06/07 18:09

Table 2.1. Character Function Summary

FunctionDescriptionASCIIReturns the ASCII decimal equivalent of a characterCHRReturns the character given the decimal equivalentCONCATConcatenates two strings; same as the operator ||INITCAPReturns the string with the first letter of each word in uppercaseINSTRFinds the numeric starting position of a string within a stringINSTRBSame as INSTR but counts bytes instead of charactersLENGTHReturns the length of a string in charactersLENGTHBReturns the length of a string in bytesLOWERConverts a string to all lowercaseLPADLeft-fills a string to a set length using a specified characterLTRIMStrips leading characters from a stringREPLACEPerforms substring search and replaceRPADRight-fills a string to a set length using a specified characterRTRIMStrips trailing characters from a stringSOUNDEXReturns a phonetic representation of a stringSUBSTRReturns a section of the specified string, specified by numeric character positionsSUBSTRBReturns a section of the specified string, specified by numeric byte positionsTRANSLATEPerforms character search and replaceTRIMStrips leading, trailing, or both leading and trailing characters from a stringUPPERConverts a string to all uppercase

1、INSTR(c1, c2[,i[,j]])其中c1，c2是字符串,i和j是可选的，这个函数返回c2在c1中从i开始数，第j次出现的位置，例如：

SELECT data_value, INSTR(data_value,'i',4,1) instr_exampleFROM sample_data;DATA_VALUE           INSTR_EXAMPLE ]]Comment-------------------- ------------- ---------------------------------------------THE three muskETeers             0 在该字符串中没有"i"ali and*41*thieves              14 由于i是从第四个位置开始数，所以处在第三个位置的i                                   也就被忽略了，14位置出现了i，所以返回该位置mississippi                      5 处在2的i被忽略了，返回5位置的imister INDIA                     0 大写I并不等同于小写的i

2、LPAD(c1, i [,c2])，c1和c2是字符串，i是整数，这个函数的作用是将c1扩展到i个长度，如果c1长度不够，则在c1的左边用c2填充，如果c1的长度大于i，则在c1右边截掉，以满足i长度。如果c2不写，默认是空格。

SELECT LPAD(last_name,10) lpad_lname,       LPAD(salary,8,'*') lpad_salaryFROM employeesWHERE last_name like 'J%';

LPAD_LNAME LPAD_SAL---------- --------   Johnson ****6200     Jones ****2800

3、LTRIM(c1 [,c2])其中c1和c2是字符串，这个函数的功能是截掉c1左边的从c2，如果c2不写，默认是截掉空格。

SELECT LTRIM('Mississippi','Mis') test1

,LTRIM('Rpadded ') test2

,LTRIM(' Lpadded') test3

,LTRIM(' Lpadded', 'Z') test4

FROM dual;TES TEST2 TEST3 TEST4

--- ----------------- ------- ------------

ppi Rpadded Lpadded Lpadded

4、REPLACE(c1, c2 [,c3])该函数的作用是讲c1中的所有c2用c3替换，如果c3是NULL，也就是说c3不存在,这将去掉c1中的所有c2，如果c2为NULL，则原封不动的返回c1，如果c1是NULL，则返回NULL。

5、SOUNDEX(c1)这个函数返回跟c1发音相似的字符串例如

SELECT first_name, last_name

FROM employees

WHERE SOUNDEX(first_name) = SOUNDEX('Stevan');

FIRST_NAME LAST_NAME

-------------------- -------------------------

Steven King

StevenMarkle

Stephen Stiles

6、SUBSTR(c1, x [, y])其中c1是字符串，x和y是数字，这个函数的功能是返回c1中从x开始数，y个字符串，如果x是负数，顺序就从右往左数，如果y没有，那就到结尾。

SELECT SUBSTR('The Three Musketeers',1,3) Part1      ,SUBSTR('The Three Musketeers',5,5) Part2

,SUBSTR('The Three Musketeers',11)  Part3      ,SUBSTR('The Three Musketeers',-5)  Part4FROM dual;PAR PART2 PART3      PART4--- ----- ---------- -----The Three Musketeers teers

7、TRANSLATE(c1, c2, c3)其中c1、c2、c3都是字符串，如果其中要是有一个为NULL，则返回NULL，这个函数的作用是将c1中的所有c2替换为c3，如果c2的长度小于c3，则在c3中未匹配的部分将在c1中被忽略，若果c2的长度大于c3，则在c2中未匹配的部分将在c1中被忽略掉。

The following example substitutes * for a, # for e, and $ for i, and it removes o and u from the last_namecolumn:

SELECT last_name, TRANSLATE(last_name, 'aeiou', '*#$') no_vowelFROM employeesWHERE last_name like 'S%';LAST_NAME                 NO_VOWEL------------------------- --------------Sarchand                  S*rch*ndSciarra                   Sc$*rr*Seo                       S#Smith                     Sm$thSullivan                  Sll$v*nSully                     Slly

Here is another example, where the case is reversed; uppercase letters are converted to lowercase, and lowercase letters are converted to uppercase:

SELECT data_value, TRANSLATE(data_value,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')FROM sample_data;DATA_VALUE           TRANSLATE(DATA_VALUE-------------------- --------------------THE three muskETeers the THREE MUSKetEERSali and*41*thieves   ALI AND*41*THIEVESmississippi          MISSISSIPPImister INDIA         MISTER india