Oracle中字符操作函数

Table 2.1. Character Function Summary

Function	Description
ASCII	Returns the ASCII decimal equivalent of a character
CHR	Returns the character given the decimal equivalent
CONCAT	Concatenates two strings; same as the operator ||
INITCAP	Returns the string with the first letter of each word in uppercase
INSTR	Finds the numeric starting position of a string within a string
INSTRB	Same as INSTR but counts bytes instead of characters
LENGTH	Returns the length of a string in characters
LENGTHB	Returns the length of a string in bytes
LOWER	Converts a string to all lowercase
LPAD	Left-fills a string to a set length using a specified character
LTRIM	Strips leading characters from a string
REPLACE	Performs substring search and replace
RPAD	Right-fills a string to a set length using a specified character
RTRIM	Strips trailing characters from a string
SOUNDEX	Returns a phonetic representation of a string
SUBSTR	Returns a section of the specified string, specified by numeric character positions
SUBSTRB	Returns a section of the specified string, specified by numeric byte positions
TRANSLATE	Performs character search and replace
TRIM	Strips leading, trailing, or both leading and trailing characters from a string
UPPER	Converts 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_example
FROM 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位置的i
mister 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_salary
FROM employees
WHERE 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)  Part4
FROM 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_vowel
FROM employees
WHERE last_name like 'S%';

LAST_NAME                 NO_VOWEL
------------------------- --------------
Sarchand                  S*rch*nd
Sciarra                   Sc$*rr*
Seo                       S#
Smith                     Sm$th
Sullivan                  Sll$v*n
Sully                     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 MUSKetEERS
ali and*41*thieves   ALI AND*41*THIEVES
mississippi          MISSISSIPPI
mister INDIA         MISTER india