程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【函數】Oracle函數系列(1)--字符函數,oracle函數

【函數】Oracle函數系列(1)--字符函數,oracle函數

編輯:Oracle教程

【函數】Oracle函數系列(1)--字符函數,oracle函數


函數Oracle函數系列(1)--字符函數

 

1  BLOG文檔結構圖

 

 

2  前言部分

 

2.1  導讀和注意事項

各位技術愛好者,看完本文後,你可以掌握如下的技能,也可以學到一些其它你所不知道的知識,~O(∩_∩)O~:

① 常見字符函數(lower、upper、initcap、concat、substr、length、intr、lpad、rpad、trim、chr、ascii、replace、translate)的使用

② 判斷字符串中是否含有漢字

③ substr和instr的聯合使用

④ 替換函數的使用

 

Tips:

① 本文在itpub(http://blog.itpub.net/26736162)、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號(xiaomaimiaolhr)上有同步更新。

② 文章中用到的所有代碼、相關軟件、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載,小麥苗的雲盤地址見:http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章代碼格式有錯亂,請嘗試以下辦法:①去博客園地址閱讀,②下載pdf格式的文檔來閱讀。

④ 在本篇BLOG中,代碼輸出部分一般放在一行一列的表格中。其中,需要特別關注的地方我都用灰色背景和粉紅色字體來表示,比如在下邊的例子中,thread 1的最大歸檔日志號為33,thread 2的最大歸檔日志號為43是需要特別關注的地方;而命令一般使用黃色背景和紅色字體標注;對代碼或代碼輸出部分的注釋一般采用藍色字體表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正,ITPUB留言或QQ皆可,您的批評指正是我寫作的最大動力。

 

2.2  相關文章鏈接

【推薦】 oracle中如何判斷一個字符串是否含有漢字

http://blog.itpub.net/26736162/viewspace-1688209/

Oracle中如何判斷一個字符串是否含有漢字(2)

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771569&idx=1&sn=5bb10af43556c18906540ffe76ba87cf&chksm=fe8bba09c9fc331f0152b38067150920e75bac8cdd13fd1a2d881fdfec835ef152b54e2c798c&scene=21#wechat_redirect

Oracle中如何判斷一個字符串是否含有漢字

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771284&idx=1&sn=ab4e725c4d4ad448e245e1668ccac382&scene=21#wechat_redirect

【函數】wm_concat包的訂制

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771521&idx=1&sn=e75d27d085892a4be051c4c2e736f97a&chksm=fe8bba39c9fc332f3a2af83918cdb2afa1c83a7faf15971ce566c4321540c38e51279dd615a2&scene=21#wechat_redirect

2.3  本文簡介

最近因為360雲盤停止個人用戶服務的事情要忙,所以等待事件系列就不更新了,轉而更新函數系列吧,畢竟函數比較簡單嘛。

本篇文章為第一篇,講解所有的字符函數。

先來一道考題:

Which four are types of functions available in SQL? (Choose 4)

A. string

B. character

C. integer

D. calendar

E. numeric

F. translation

G. date

H. conversion

Answer: B,E,G,H

 

3  字符函數 

字符函數是 oracle 中最常用的函數,我們來看看有哪些字符函數:

 

(一)大小寫控制函數

l lower(char):將字符串全部轉化為小寫的格式。

l upper(char):將字符串全部轉化為大寫的格式。

l initcap('SQL course') 每個單詞的首字母大寫,其余變為小寫,結果:Sql Course,

 

(二)字符控制函數

l concat('Hello','World') 字符串連接,結果:HelloWorld

l length(char):返回字符串的長度。

l substr(char,m,n):取字符串的子串,m表示起點,n 代表取 n 個字符的意思

l replace(char1,search_string,replace_string) 替換

l instr(char1,char2,[,n[,m]]) 取子串在字符串的位置(特別取某一個特殊字符在原字符串中的位置)

l trim('    Hello   World    '), 結果為: “Hello   World”

l ltrim('    Hello   World    '),結果為: “Hello   World    ”

l rtrim('    Hello   World    '),結果為: “    Hello   World”

l lpad(salary,10,'*') 結果:*****24000

l rpad(salary, 10, '*') 結果:24000*****

l chr()函數將ASCII碼轉換為字符: ASCII碼 –》 字符;

l ascii()函數將字符轉換為ASCII碼:字符 –》 ASCII碼;

 

3.0.1   lower: 將字符串全部轉化為小寫的格式

SQL> select lower('Hello WORLD') from dual;

 

LOWER('HELL

-----------

hello world

 

SQL> Select ENAME,LOWER(ENAME) From SCOTT.EMP;

 

ENAME      LOWER(ENAM

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

SMITH      smith

ALLEN      allen

WARD       ward

JONES      jones

MARTIN     martin

BLAKE      blake

CLARK      clark

SCOTT      scott

KING       king

TURNER     turner

ADAMS      adams

JAMES      james

FORD       ford

MILLER     miller

 

 

3.0.2   upper: 將字符串全部轉化寫的格式

經常用於比較字符串的內容:

SQL> select upper('Hello WORLD') from dual;

 

UPPER('HELL

-----------

HELLO WORLD

 

SQL> select ename,sal,hiredate from scott.emp where lower(ename) = 'ward';

 

ENAME             SAL HIREDATE

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

WARD             1250 1981-02-22 00:00:00

 

3.0.3   initcap:每個字符串的首字母大寫,其余變為小寫

select initcap('SQL coURse ') from dual;

結果:

 

 

3.0.4   concat:連接兩個不同的列,concat只能接受兩個參數

SQL> Select CONCAT(EMPNO,ENAME) From SCOTT.EMP;

 

CONCAT(EMPNO,ENAME)

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

7369SMITH

7499ALLEN

7521WARD

7566JONES

7654MARTIN

7698BLAKE

7782CLARK

7788SCOTT

7839KING

7844TURNER

7876ADAMS

7900JAMES

7902FORD

7934MILLER

 

14 rows selected.

 

SQL> Select CONCAT('A','B') From dual;

 

CO

--

AB

 

SQL> select concat(concat('A','B'),'c') from dual;--可以嵌套使用

 

CON

---

ABc

可以使用連字符 ||  來連接多列:

SELECT 'A'||'B' FROM dual;

 

 

 

3.0.5   substr:截取子串

下標從1開始,這個函數有三個參數,允許你將目標字符串的一部份輸出,第一個參數為目標字符串,第二個參數是將要輸出的子串的起點,第三個參數是將要輸出的子串的長度,如果沒有第三個參數,則余下的字符全部輸出。

注意:如果第二個參數為負數 那麼將會從目標字符串的尾部開始向前定位至負數的絕對值的位置

Select SUBSTR(ENAME,2) From EMP--從第2個位置截到末尾

Select SUBSTR(ENAME,2,3) From EMP--從第2個位置截取3個字母

 

SELECT SUBSTR('lihuarong',0,3) col_1,

 SUBSTR('lihuarong',1,3)col_2,

        SUBSTR('lihuarong',3)col_3,

 SUBSTR('lihuarong',-4,3) col_4

FROM DUAL;

SYS@raclhr1> SELECT SUBSTR('lihuarong',0,3) col_1,

  2              SUBSTR('lihuarong',1,3)col_2,

  3          SUBSTR('lihuarong',3)col_3,

  4              SUBSTR('lihuarong',-4,3) col_4

  5  FROM DUAL;

 

COL COL COL_3   COL

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

lih lih huarong ron

 

注意: 如果第二個參數大於第一個參數的長度,則第一個參數從該列默認的長度開始計算。如下例的name列默認的長度為10

 

select name,substr(name,-4,3),

       substr(name,-5,3) 

from dual;

 

3.0.6   length:求字符串長度

SELECT length('重慶貴陽30N重慶貴陽30N-77623') FROM dual;

 

 

SELECT length(77623) FROM dual;

 

 

SELECT length(sysdate),sysdate FROM dual;

 

 

SELECT length(1/2) FROM dual;

 

 

SELECT length('1/2'),

       length(1 / 2),

       1 / 2,

 length(0.5)

FROM   dual;

 

一、 Oracle中 length 和 lengthb 區別 

The LENGTH functions return the length of char. LENGTH calculates length usingcharacters as defined by the input character set.  --返回以字符為單位的長度.

LENGTHB usesbytes instead of characters.  --返回以字節為單位的長度.

LENGTHC usesUnicode complete characters. --返回以Unicode完全字符為單位的長度.

LENGTH2 usesUCS2 code points.--返回以UCS2代碼點為單位的長度.

LENGTH4 usesUCS4 code points.  --返回以UCS4代碼點為單位的長度.

 

char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are LENGTHC, LENGTH2, and LENGTH4, which do not allow char to be a CLOB or NCLOB. The return value is of data type NUMBER. If char has data type CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

Restriction on LENGTHB The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.

Examples

The following example uses the LENGTH function using a single-byte database character set:

SELECT LENGTH('CANDIDE') "Length in characters"

  FROM DUAL;

Length in characters

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

                   7

The next example assumes a double-byte database character set.

SELECT LENGTHB ('CANDIDE') "Length in bytes"

  FROM DUAL;

 

Length in bytes

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

             14

 

SQL> select length('北京') from dual;

LENGTH('北京')

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

             2

SQL> select lengthb('北京') from dual;

LENGTHB('北京')

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

              6

SQL> select lengthb('BeiJing') from dual;

LENGTHB('BEIJING')

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

                 7

SQL> select length('BeiJing') from dual;

LENGTH('BEIJING')

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

                7

 

值得一提的是:

在不同的數據庫,因為字符集的不同,LENGTHB得到的值可能會不一樣。如ZHS16GBK采用兩個byte位來定義一個漢字。而在UTF8,采用3個byte。

--查看字符集語句

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER                      VALUE

select lengthb('齊'),length('齊') from dual

在計算單字節的字符串時。返回的值是相同的。

select lengthb('string'),length('string') from dual

2 、通過對同一個字符串的長度判斷

lengthb(string)=length(string)

可以判斷是否含有漢字。

例子

SELECT lengthb('齊'),

       length('齊')

FROM   dual;

 

 

SELECT l.code,

       length(l.code),

       lengthb(l.code),

       length2(l.code),

       length4(l.code),

       lengthc(l.code)

FROM   xb_link l

WHERE  length(l.code) != lengthb(l.code)

AND    length(l.code) < 20;

 

二、 判斷字符串中是否含有漢字

參考:

【推薦】 oracle中如何判斷一個字符串是否含有漢字

http://blog.itpub.net/26736162/viewspace-1688209/

Oracle中如何判斷一個字符串是否含有漢字(2)

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771569&idx=1&sn=5bb10af43556c18906540ffe76ba87cf&chksm=fe8bba09c9fc331f0152b38067150920e75bac8cdd13fd1a2d881fdfec835ef152b54e2c798c&scene=21#wechat_redirect

Oracle中如何判斷一個字符串是否含有漢字

http://mp.weixin.qq.com/s?__biz=MzIzOTA2NjEzNQ==&mid=2454771284&idx=1&sn=ab4e725c4d4ad448e245e1668ccac382&scene=21#wechat_redirect

 

 

3.0.7   instr :相當於String類中的indexOf,求索引

如果需要知道在一個字符串中滿足特定的內容的子串位置可以使用INSTR,它的第一個參數是目標字符串, 第二個參數是匹配的內容,第三和第四個參數是數字,用以指定開始搜索的起點以及指出第幾個滿足條件的將會被返回。

默認第三個與第四個參數的數值均為1,如果第三個數值為負數那麼將會從後向前搜索。

如果未匹配到則返回0;

SELECT INSTR('HelloWHoldhH', 'H', 6, 2) l1, --從第6個位置開始,第2次出現H的位置

       INSTR('HelloWHoldhH', 'H') l2, --從第1個位置開始,第1次出現H的位置

       INSTR('HelloWHoldhH', 'H', 2) l3, --從第2個位置開始,第1次出現H的位置

       INSTR('HelloWHoldhH', 'H', -1) l4, --從倒數第1個位置開始從右往左搜索,第1次出現H的位置

       INSTR('HelloWHoldhH','/')  l5--從第1個位置開始,第1次出現H的位置,如果匹配不到則返回0

FROM   DUAL;

SYS@raclhr1> SELECT INSTR('HelloWHoldhH', 'H', 6, 2) l1, --從第6個位置開始,第2次出現H的位置

  2         INSTR('HelloWHoldhH', 'H') l2, --從第1個位置開始,第1次出現H的位置

  3         INSTR('HelloWHoldhH', 'H', 2) l3, --從第2個位置開始,第1次出現H的位置

  4         INSTR('HelloWHoldhH', 'H', -1) l4, --從倒數第1個位置開始從右往左搜索,第1次出現H的位置

  5         INSTR('HelloWHoldhH','/')  l5--從第1個位置開始,第1次出現H的位置,如果匹配不到則返回0

  6  FROM   DUAL;

        L1         L2         L3         L4         L5

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

        12          1          7         12          0

 

 

 

 

注意:無論是從前向後還是從後向前搜索,返回的數值都是從左到右的數值。

 

一、 特殊用法

SELECT * FROM Dba_Objects d WHERE  instr('5,6,7', d.object_id) > 0;

相當於:

SELECT * FROM Dba_Objects d WHERE d.object_id=5 OR d.object_id=6 OR d.object_id=7;

 

二、 substr 和instr 的聯合使用

這2個函數常常關聯使用,但是存在如果instr匹配不到字符串的話返回的就是0,這樣substr得到的值就是空值,所以這個時候就應該進行轉換,使用decode或其它函數來轉化。

SELECT NAME,

       substr(NAME, 1, instr(NAME, 'a') - 1),

       decode(instr(NAME, 'a'),

              0,

              NAME,

              substr(NAME, 1, instr(NAME, 'a') - 1))

FROM   xb_b;

 

 

3.0.8   LPAD和RPAD的用法

表示補充的含義,這兩個函數最少需要兩個參數,最多需要三個參數,第一個參數是需要處理的字符串,第二個參數是需要將字符串擴充的寬度,第三個參數表示加寬部分用什麼字符來做填補,第三個參數的默認值為空格,但也可以是單個的字符或字符串

Select LPAD(SAL,10,'*') From EMP

Select RPAD(SAL,10,'*') From EMP

將sal顯示為10位,不足的位置補上字符*

 

補充:如果限定的位數在比字符的個數少,那麼截取字符串的右側字母

      select ename,lpad(ename,5,'#') from emp;

 

select lpad(name,10),lpad(name,10,'*'),lpad(value,10,'*'),lpad(name,2,'&'), 

        rpad(name,10),rpad(name,10,'*'),rpad(value,10,'*'),rpad(name,2,'&')

from dual;

 

 

 

3.0.9   trim兩邊濾空

LTRIM和RTRIM至少需要一個參數最多允許兩個參數第一個參數與LPAD和RPAD類似是一個字符串 第二個參數也是一個字符或字符串默認則是空格 如果第二個參數不是空格的話 那麼該函數將會像剪除空格那樣剪除所指定的字符

使用LTrime和RTrim過濾一邊的空格

SELECT LTRIM('   A B C      ') L ,RTRIM('    HELLO    WORLD    ') R,TRIM('    HELLO    WORLD    ') FROM DUAL;

SQL> SELECT LTRIM('   A B C      ') L ,RTRIM('    HELLO    WORLD    ') R,TRIM('    HELLO    WORLD    ') FROM DUAL;

L           R                  TRIM('HELLOWOR

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

A B C           HELLO    WORLD HELLO    WORLD

 

一、  trim其它用法

TRIM([ { { LEADING | TRAILING | BOTH }

      [ trim_character ]

   | trim_character

   }

   FROM

    ]

    trim_source

)

 

SELECT TRIM('   a|a  ') C1,

       TRIM(BOTH FROM('   a|a  ')) C2,

       TRIM(TRAILING FROM('   a|a  ')) C3,

       TRIM(LEADING FROM('   a|a  ')) C4,

       TRIM('X' FROM 'XX  a|a  XX') C5,

       TRIM(BOTH 'X' FROM 'XX  a|a  XX') C6,

       TRIM(TRAILING 'X' FROM 'XX  a|a  XX') C7,

       TRIM(LEADING 'X' FROM 'XX  a|a  XX') C8,

       RTRIM('XYXXXY  a|a  XXXY', 'XY') C9,

       LTRIM('XYXXXY  a|a  XXXY', 'XY') C10,

       TRIM(BOTH FROM REPLACE(' a|a', '|', '')) C11,

       TRIM(BOTH ' ' FROM REPLACE(' a|a', '|', '')) C12,

       TRIM(BOTH 'a' FROM REPLACE(' a|a', '|', '')) C13

  FROM DUAL;

 

BOTH參數表示同時去除字符串前後所指定的內容(默認情況下刪除空格)。“TRAILING”參數可以完成字符串尾部空格的刪除功能,而“LEADING”參數可以完成字符串頭部空格的刪除功能。也就是說,使用“TRAILING”和“LEADING”參數可以指定空格的刪除位置。

SQL> SELECT TRIM('   a|a  ') C1,

  2         TRIM(BOTH FROM('   a|a  ')) C2,

  3         TRIM(TRAILING FROM('   a|a  ')) C3,

  4         TRIM(LEADING FROM('   a|a  ')) C4,

  5         TRIM('X' FROM 'XX  a|a  XX') C5,

  6         TRIM(BOTH 'X' FROM 'XX  a|a  XX') C6,

  7         TRIM(TRAILING 'X' FROM 'XX  a|a  XX') C7,

  8         TRIM(LEADING 'X' FROM 'XX  a|a  XX') C8,

  9         RTRIM('XYXXXY  a|a  XXXY', 'XY') C9,

10         LTRIM('XYXXXY  a|a  XXXY', 'XY') C10,

11         TRIM(BOTH FROM REPLACE(' a|a', '|', '')) C11,

12         TRIM(BOTH ' ' FROM REPLACE(' a|a', '|', '')) C12,

13         TRIM(BOTH 'a' FROM REPLACE(' a|a', '|', '')) C13

14    FROM DUAL;

 

C1  C2  C3     C4    C5      C6      C7        C8        C9            C10         C1 C1 C

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

a|a a|a    a|a a|a     a|a     a|a   XX  a|a     a|a  XX XYXXXY  a|a     a|a  XXXY aa aa

 

 

 

3.0.10   chr()ascii()

chr()函數將ASCII碼轉換為字符:ASCII碼 –》 字符;

ascii()函數將字符轉換為ASCII碼: 字符 –》 ASCII碼;

在oracle中chr()函數和ascii()是一對反函數。

SQL> select chr(65) from dual;

 

CHR(65)

-------

A

SQL> select ascii('A') from dual;

 

ASCII('A')

----------

        65

 

SELECT chr(5), 

       chr(10), --換行

       chr(13)  --回車

FROM   dual;

 

 

 

3.0.11   替換函數

Oracle常用替換函數有2個,分別是REPLACE和TRANSLATE函數。REPLACE與TRANSLATE都是替代函數,只不過REPLACE針對的是字符串,而TRANSLATE針對的是單個字符,下邊我們分別講解這2個函數的用法。

一、 REPLACE:替換

它的工作就如果它的名字所說的那樣,該函數需要三個參數,第一個參數是需要搜索的字符串,第二個參數是搜索的內容,第三個參數則是需要替換成的字符串,如果第三個參數省略,則默認為空格,如果第二個參數是NULL,那麼將只執行搜索操作而不會替換任何內容。

 

SELECT REPLACE('HelloWorld','o','A') FROM DUAL;

SELECT ENAME ,REPLACE(ename,'A','B') FROM EMP;

SELECT REPLACE('lihuarong','ua','AA') col_1,

       REPLACE('lihuarong','on')col_2,

       REPLACE('lihuarong',null) col_3

FROM DUAL;

SYS@raclhr1> SELECT REPLACE('lihuarong','ua','AA') col_1,

  2         REPLACE('lihuarong','on') col_2,

  3         REPLACE('lihuarong',null) col_3

  4  FROM DUAL;

 

COL_1      COL_2    COL_3

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

lihAArong lihuarg lihuarong

 

 

二、 TRANSLATE

語法:TRANSLATE(char, from, to)

 

用法:

1. 返回將出現在from中的每個字符替換為to中的相應字符以後的字符串。

2. 若from比to字符串長,那麼在from中比to中多出的字符將會被刪除,或者認為from中多出的字符在to中與空對應

3. 三個參數中有一個是空,返回值也將是空值。

 

 

舉例:SQL> select translate('abcdefga','abc','wo') 返回值 from dual;

            返回值

           -------

           wodefgw

 

分析:該語句要將'abcdefga'中的'abc'轉換為'wo',由於'abc'中'a'對應'wo'中的'w',故將'abcdefga'中的'a'全部轉換成'w'; 而'abc'中'b'對應'wo'中的'o',故將'abcdefga'中的'b'全部轉換成'o';'abc'中的'c'在'wo'中沒有與之對應的字符,故將'abcdefga'中的'c'全部刪除;

 

簡單說來,就是將from中的字符轉換為to中與之位置對應的字符,若to中找不到與之對應的字符,返回值中的該字符將會被刪除。

09:43:50 SQL>  select translate('abcdefga','abc','wo')  from dual;

 

TRANSLA

-------

wodefgw

 

Elapsed: 00:00:00.14

09:43:57 SQL>  select translate('abcdefga','abc','')  from dual;

 

T

-

 

 

Elapsed: 00:00:00.00

 

 

 

SELECT translate('ab 你好 bcadefg','abcdefg','1234567'),translate('ab 你好 bcadefg','1abcdefg','1') FROM dual;

 

1、 按數字和字母混合字符串中的字母排序

 

09:52:01 SQL> create or replace view v as select empno || ' '||ename as data from scott.emp;

 

View created.

 

Elapsed: 00:00:00.54

09:52:07 SQL> select * from V

09:52:15   2  ;

 

DATA

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

9000 lastwiner

9001 lastwiner

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

7934 MILLER

 

16 rows selected.

 

Elapsed: 00:00:00.20

 

09:55:07 SQL> select data,translate(data,'- 0123456789','-') from V order by 2;

 

DATA                                                TRANSLATE(DATA,'-0123456789','-')

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

7876 ADAMS                                          ADAMS

7499 ALLEN                                          ALLEN

7698 BLAKE                                          BLAKE

7782 CLARK                                          CLARK

7902 FORD                                           FORD

7900 JAMES                                          JAMES

7566 JONES                                          JONES

7839 KING                                           KING

7654 MARTIN                                         MARTIN

7934 MILLER                                         MILLER

7788 SCOTT                                          SCOTT

7369 SMITH                                          SMITH

7844 TURNER                                         TURNER

7521 WARD                                           WARD

9001 lastwiner                                      lastwiner

9000 lastwiner                                      lastwiner

 

16 rows selected.

 

 

2、 從字符串中刪除不需要的字符

去掉AEIOU字符:

SELECT a.ename,translate(a.ename,'1AEIOU','1'),regexp_replace(a.ename,'[AEIOU]') FROM scott.emp a WHERE a.deptno=10;

 

3、 將字符和數字分離(只保留數字、只保留數字以外的其它字符)

drop table dept2 purge;

create table dept2 as

select dname || deptno as data from scott.dept;

 

 

SELECT a.data,

       regexp_replace(a.data, '[0-9]') dname,

       regexp_replace(a.data, '[^0-9]') deptno,

       translate(a.data, 'a0123456789', 'a') dname,

       translate(a.data, '0123456789' || data, '0123456789') deptno

FROM   dept2 a;

 

 

4、 提取姓名的大寫首字母縮寫

create or replace view v as 

select 'Michael Hartstein' as al from dual;

 

select regexp_replace(v.al,'([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') as sx from v;

select  translate(al,' '||a2,'.') as sx from (select al ,lower(al) as a2 from v);

 

 

 

 

三、 總結

總結:replace與translate都是替代函數,只不過replace針對的是字符串,而translate針對的是單個字符。

 

 

 

 

3.0.12   字符函數綜合案例

問題:將所有員工的名字按小寫的方式顯示

SQL>  select  lower(ename)  from  emp;

 

問題:將所有員工的名字按大寫的方式顯示。

SQL>  select  upper(ename)  from  emp;

 

問題:顯示正好為 5 個字符的員工的姓名。

SQL>  select  *  from  emp  where  length(ename)=5;

 

問題:顯示所有員工姓名的前三個字符。

SQL>  select  substr(ename,1,3)  from  emp;

 

問題:以首字母大寫,後面小寫的方式顯示所有員工的姓名。

SQL>  select  upper(substr(ename,1,1))  ||

lower(substr(ename,2,length(ename)-1))from  emp;

 

問題:以首字母小寫,後面大寫的方式顯示所有員工的姓名。

SQL>  select  lower(substr(ename,1,1))  ||

upper(substr(ename,2,length(ename)-1))from  emp;

 

問題:顯示所有員工的姓名,用“我是老虎”替換所有“A”

SQL>  select  replace(ename,'A',  '我是老虎')  from  emp;

 

如下表(表名 :lhr_str):需要將name和code重新命名,規則為:將第一個‘/’之前的內容換成“A端原命名-Z端原命名”,如果沒有‘/’則不更改!

 

BEGIN

    FOR cr IN (SELECT * FROM lhr_str) LOOP

   

        UPDATE lhr_str l

        SET    l.new_name = l.a端原命名 || '-' || l.z端原命名 ||

                            substr(l.name, instr(l.name, '/'))

        WHERE  instr(l.name, '/') <> 0; --這裡一定要加where條件否則全部更新,下面的類似

        UPDATE lhr_str l

        SET    l.new_code = l.a端原命名 || '-' || l.z端原命名 ||

                            substr(l.code, instr(l.code, '/'))

        WHERE  instr(l.code, '/') <> 0;

        UPDATE lhr_str l

        SET    l.new_name = l.name

        WHERE  instr(l.name, '/') = 0;

        UPDATE lhr_str l

        SET    l.new_code = l.code

        WHERE  instr(l.Code, '/') = 0;

    END LOOP;

END;

 

 

About Me

...............................................................................................................................

● 本文作者:小麥苗,只專注於數據庫的技術,更注重技術的運用

● 本文在itpub(http://blog.itpub.net/26736162)、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2126927/

● 本文博客園地址:http://www.cnblogs.com/lhrbest/p/5988552.html

● 本文pdf版及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

● 聯系我請加QQ好友(642808185),注明添加緣由

● 於 2016-10-22 15:00 ~ 2016-10-22 23:00 在泰興公寓完成

● 文章內容來源於小麥苗的學習筆記,部分整理自網絡,若有侵權或不當之處還請諒解

● 【版權所有,文章允許轉載,但須以鏈接方式注明源地址,否則追究法律責任】

...............................................................................................................................

手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號:xiaomaimiaolhr,免費學習最實用的數據庫技術。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved