Oracle SQL函数之字符串函数

1.SQL> select ASCII('a'),ASCII('A'),CHR(97) from dual; --ASCLL（x）返回x的ASCLL码，CHR(x)返回ASCLL码为x的字符
ASCII('A') ASCII('A') CHR(97)
---------- ---------- -------
        97         65 a

2.SQL> SELECT NAME,INSTR(NAME,'Science') FROM PRODUCTS WHERE PRODUCT_ID=1;  --显示字符串Science出现的位置
NAME                           INSTR(NAME,'SCIENCE')
------------------------------ ---------------------
Modern Science                                     8

3.SQL>  SELECT NAME,INSTR(NAME,'e',1,2) FROM PRODUCTS WHERE NAME LIKE'%e%e%';  --1表示从第1个字符开始，2表示出现次数2次
NAME                           INSTR(NAME,'E',1,2)
------------------------------ -------------------
Modern Science                                  11
2412: The Return                                12
Space Force 9                                   11
From Another Planet                             18
Creative Yell                                    8

4.SQL>  SELECT CONCAT(FIRST_NAME || ' ', LAST_NAME) FROM CUSTOMERS; --concat(x,y)连接字符串xy
CONCAT(FIRST_NAME||'',LAST_NAM
------------------------------
Cynthis Green
JOHN BROWN
Doreen Blue
Steve White
GAIL BLACK
%test test
6 rows selected

5.SQL> SELECT INITCAP(DESCRIPTION)  FROM PRODUCTS WHERE PRODUCT_ID<3;   --inicat(x)，首字母大写
INITCAP(DESCRIPTION)
--------------------------------------------------
A Description Of Modern Science
Introduction To Chemistry


6.SQL> SELECT LENGTH(NAME) FROM PRODUCTS; --length(x），返回x的长度
LENGTH(NAME)
------------
          14
           9
           9
           8
           7
          16
          13
          19
          15
           5
          13
          13
12 rows selected

7.SQL> SELECT NAME,LOWER(NAME),UPPER(NAME) FROM PRODUCTS;  --转换为大小写
NAME                           LOWER(NAME)                    UPPER(NAME)
------------------------------ ------------------------------ ------------------------------
Modern Science                 modern science                 MODERN SCIENCE
Chemistry                      chemistry                      CHEMISTRY
Supernova                      supernova                      SUPERNOVA
Tank War                       tank war                       TANK WAR
Z Files                        z files                        Z FILES
2412: The Return               2412: the return               2412: THE RETURN
Space Force 9                  space force 9                  SPACE FORCE 9
From Another Planet            from another planet            FROM ANOTHER PLANET
Classical Music                classical music                CLASSICAL MUSIC
Pop 3                          pop 3                          POP 3
Creative Yell                  creative yell                  CREATIVE YELL
My Front Line                  my front line                  MY FRONT LINE
12 rows selected

8.SQL> SELECT RPAD(NAME,20,'-'),LPAD(PRICE,10,'*') FROM PRODUCTS;  --RPAD(x，width,string)右填充，RPAD(x,width,string)左填充
RPAD(NAME,20,'-')                        LPAD(PRICE,10,'*')
---------------------------------------- --------------------
Modern Science------                     *****19.95
Chemistry-----------                     ********30
Supernova-----------                     *****25.99
Tank War------------                     *****13.95
Z Files-------------                     *****49.99
2412: The Return----                     *****14.95
Space Force 9-------                     *****13.49
From Another Planet-                     *****12.99
Classical Music-----                     *****10.99
Pop 3---------------                     *****15.99
Creative Yell-------                     *****14.99
My Front Line-------                     *****13.49
12 rows selected

9.SQL> SELECT LTRIM('    HELLO RUSKY'),RTRIM('HELLO RUSKY ABCDE','EDCB'),TRIM('AB' FROM 'ABC HELLO RUSKY ABCDEA') FROM DUAL;
SELECT LTRIM('    HELLO RUSKY'),RTRIM('HELLO RUSKY ABCDE','EDCB'),TRIM('AB' FROM 'ABC HELLO RUSKY ABCDEA') FROM DUAL
ORA-30001: 截取集仅能有一个字符

SQL> SELECT LTRIM('    HELLO RUSKY'),RTRIM('HELLO RUSKY ABCDE','EDCB'),TRIM('A' FROM 'ABC HELLO RUSKY ABCDEA') FROM DUAL;
LTRIM('HELLORUSKY') RTRIM('HELLORUSKYABCDE','EDCB' TRIM('A'FROM'ABCHELLORUSKYABCD
------------------- ------------------------------ ------------------------------
HELLO RUSKY         HELLO RUSKY A                  BC HELLO RUSKY ABCDE

  SQL> select ltrim('abcdefg','abc')from dual;
  LTRIM('ABCDEFG','ABC')
  ----------------------
  defg

说明：LTRIM(x,string)从X左边开始截去string中的字符串  RTRIM(x,string)从X右边开始截去string中的字符串   TRIM(string FROM x)从x的左右两边截去string   这三个函数，如果不指定string，则默认截去x中的空格。

 
10.SQL> SELECT CUSTOMER_ID,NVL(PHONE,'Unknown Phone Number') FROM CUSTOMERS;   --NVL(x,value),如果x为空，则返回values
                            CUSTOMER_ID NVL(PHONE,'UNKNOWNPHONENUMBER'
--------------------------------------- ------------------------------
                                      2 800-555-1212
                                      1 11223344
                                      5 Unknown Phone Number
                                      3 800-555-1213
                                      4 1234
                                      6 Unknown Phone Number
6 rows selected

11.SQL> SELECT CUSTOMER_ID,NVL2(PHONE,'Known','Unknown') FROM CUSTOMERS; --NVL2(x,value1,value2)如果x非空，返回value1,如果x为空，返回value2
                            CUSTOMER_ID NVL2(PHONE,'KNOWN','UNKNOWN')
--------------------------------------- -----------------------------
                                      2 Known
                                      1 Known
                                      5 Unknown
                                      3 Known
                                      4 Known
                                      6 Unknown
6 rows selected

12.SQL> SELECT NAME,REPLACE(NAME,'Science','Physics') FROM PRODUCTS WHERE PRODUCT_ID=1; --REPLACE(x,search_string,replace_string),在x中查找search_string,并替换为replace_string
NAME                           REPLACE(NAME,'SCIENCE','PHYSIC
------------------------------ --------------------------------------------------------------------------------
Modern Science                 Modern Physics


13.SQL> SELECT LAST_NAME FROM CUSTOMERS WHERE SOUNDEX(LAST_NAME)=SOUNDEX('WHYTE'); --SOUNDEX(x)查找发音相似的单词
LAST_NAME
----------
White


14.SQL> SELECT NAME,SUBSTR(NAME,2,7) FROM PRODUCTS;   --SUBSTR(x,start,length)从x中的start位置，截取指定长度length的字符
NAME                           SUBSTR(NAME,2,7)
------------------------------ ----------------
Modern Science                 odern S
Chemistry                      hemistr
Supernova                      upernov
Tank War                       ank War
Z Files                         Files
2412: The Return               412: Th
Space Force 9                  pace Fo
From Another Planet            rom Ano
Classical Music                lassica
Pop 3                          op 3
Creative Yell                  reative
My Front Line                  y Front
12 rows selected

15.SQL> SELECT NAME,SUBSTR(NAME,2) FROM PRODUCTS;  --如果不指定length，则从第2个字符截取后面全部
NAME                           SUBSTR(NAME,2)
------------------------------ ----------------------------------------------------------
Modern Science                 odern Science
Chemistry                      hemistry
Supernova                      upernova
Tank War                       ank War
Z Files                         Files
2412: The Return               412: The Return
Space Force 9                  pace Force 9
From Another Planet            rom Another Planet
Classical Music                lassical Music
Pop 3                          op 3
Creative Yell                  reative Yell
My Front Line                  y Front Line
12 rows selected

15.SQL> SELECT NAME,UPPER(SUBSTR(NAME,2,8)) FROM PRODUCTS;  --使用组合函数
NAME                           UPPER(SUBSTR(NAME,2,8))
------------------------------ -----------------------
Modern Science                 ODERN SC
Chemistry                      HEMISTRY
Supernova                      UPERNOVA
Tank War                       ANK WAR
Z Files                         FILES
2412: The Return               412: THE
Space Force 9                  PACE FOR
From Another Planet            ROM ANOT
Classical Music                LASSICAL
Pop 3                          OP 3
Creative Yell                  REATIVE
My Front Line                  Y FRONT
12 rows selected