Oracle SQL函数之字符串函数
2024-10-17 14:33:22
1.SQL> select ASCII('a'),ASCII('A'),CHR() from dual; --ASCLL（x）返回x的ASCLL码，CHR(x)返回ASCLL码为x的字符
ASCII('A') ASCII('A') CHR()
---------- ---------- -------
a
2.SQL> SELECT NAME,INSTR(NAME,'Science') FROM PRODUCTS WHERE PRODUCT_ID=; --显示字符串Science出现的位置
NAME INSTR(NAME,'SCIENCE')
------------------------------ ---------------------
Modern Science
3.SQL> SELECT NAME,INSTR(NAME,'e',1,2) FROM PRODUCTS WHERE NAME LIKE'%e%e%'; --1表示从第1个字符开始，2表示出现次数2次
NAME INSTR(NAME,'E',,)
------------------------------ -------------------
Modern Science
: The Return
Space Force
From Another Planet
Creative Yell
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
rows selected
5.SQL> SELECT INITCAP(DESCRIPTION) FROM PRODUCTS WHERE PRODUCT_ID<; --inicat(x)，首字母大写
INITCAP(DESCRIPTION)
--------------------------------------------------
A Description Of Modern Science
Introduction To Chemistry
6.SQL> SELECT LENGTH(NAME) FROM PRODUCTS; --length(x），返回x的长度
LENGTH(NAME)
------------
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
: The Return : the return : THE RETURN
Space Force space force SPACE FORCE
From Another Planet from another planet FROM ANOTHER PLANET
Classical Music classical music CLASSICAL MUSIC
Pop pop POP
Creative Yell creative yell CREATIVE YELL
My Front Line my front line MY FRONT LINE
rows selected
8.SQL> SELECT RPAD(NAME,20,'-'),LPAD(PRICE,10,'*') FROM PRODUCTS; --RPAD(x，width,string)右填充，RPAD(x,width,string)左填充
RPAD(NAME,,'-') LPAD(PRICE,,'*')
---------------------------------------- --------------------
Modern Science------ *****19.95
Chemistry----------- ********
Supernova----------- *****25.99
Tank War------------ *****13.95
Z Files------------- *****49.99
: The Return---- *****14.95
Space Force ------- *****13.49
From Another Planet- *****12.99
Classical Music----- *****10.99
Pop --------------- *****15.99
Creative Yell------- *****14.99
My Front Line------- *****13.49
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'
--------------------------------------- ------------------------------
-- Unknown Phone Number
-- Unknown Phone Number
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')
--------------------------------------- -----------------------------
Known
Known
Unknown
Known
Known
Unknown
rows selected 12.SQL> SELECT NAME,REPLACE(NAME,'Science','Physics') FROM PRODUCTS WHERE PRODUCT_ID=; --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,,)
------------------------------ ----------------
Modern Science odern S
Chemistry hemistr
Supernova upernov
Tank War ank War
Z Files Files
: The Return : Th
Space Force pace Fo
From Another Planet rom Ano
Classical Music lassica
Pop op
Creative Yell reative
My Front Line y Front
rows selected 15.SQL> SELECT NAME,SUBSTR(NAME,2) FROM PRODUCTS; --如果不指定length，则从第2个字符截取后面全部
NAME SUBSTR(NAME,)
------------------------------ ----------------------------------------------------------
Modern Science odern Science
Chemistry hemistry
Supernova upernova
Tank War ank War
Z Files Files
: The Return : The Return
Space Force pace Force
From Another Planet rom Another Planet
Classical Music lassical Music
Pop op
Creative Yell reative Yell
My Front Line y Front Line
rows selected 15.SQL> SELECT NAME,UPPER(SUBSTR(NAME,2,8)) FROM PRODUCTS; --使用组合函数
NAME UPPER(SUBSTR(NAME,,))
------------------------------ -----------------------
Modern Science ODERN SC
Chemistry HEMISTRY
Supernova UPERNOVA
Tank War ANK WAR
Z Files FILES
: The Return : THE
Space Force PACE FOR
From Another Planet ROM ANOT
Classical Music LASSICAL
Pop OP
Creative Yell REATIVE
My Front Line Y FRONT
rows selected
最新文章
- JAVA的正则表达式-学习
- Thrift 2中get用法的详细解析
- Longest Palindromic Substring
- linux代码段，数据段，BSS段, 堆，栈（二）
- bmp格式图片文件读取
- How to use 'crontab' command on bitnami
- 让Windows Server 2008 + IIS 7+ ASP.NET 支持10万个同时请求
- mount 挂载光盘
- grails框架中在使用domain的save方法保存时保存不成功
- JavaScript 高级程序设计（第3版）笔记——chapter6：面向对象的程序设计
- 9、Cocos2dx 3.0游戏开发找小三之工厂方法模式与对象传值
- Cannot create PoolableConnectionFactory (ORA-28040: No matching authentication protocol
- iOS开发之iOS程序的启动过程
- 开源代码学习之Tinyhttpd
- 新概念英语（1-13）A new dress
- PHP微信公众号JSAPI网页支付（上）
- xml-dtd
- mybatis查询结果和接收的不一样
- HDU 1501 Zipper 字符串
- SharePoint 列表权限控制