oracle的函数

偏执的太偏执、 2023-01-19 03:07 143阅读 0赞

### 1 nvl ###

将一个null值转换为一个实际的值

nvl(commision,0)

### 2 substr ###

返回：ABC，截取从A开始3个字符

substr("ABCDEFG", 0, 3)

### 3 instr ###

是否包含某个字符

select * from students where instr(address, 'beijing') > 0

### 4 length ###

select lengthb('飘') from dual

length(string)计算string所占的字符长度：返回字符串的长度，单位是字符

lengthb(string)计算string所占的字节长度：返回字符串的长度，单位是字节

### 5 wm\_concat ###

select id,wm_concat(name) name from test group by id;

![1d880db3d413668622fc668d96fd15e3.png][]

## ##

### 6 to\_char ###

日期转换

to\_char(sysdate, 'yyyy-MM-dd   HH24:mi:ss ')

### 7 to\_number  ###

查找最大数字

SELECT to\_number(REGEXP\_REPLACE(SID,'\[^-0-9\]','')) SID2 FROM table\_name ORDER BY SID2 DESC

转载地址：

[https://www.cnblogs.com/sumsen/archive/2012/05/30/2525767.html][https_www.cnblogs.com_sumsen_archive_2012_05_30_2525767.html] (nvl)

[https://www.cnblogs.com/gsk99/archive/2011/06/14/2080573.html][https_www.cnblogs.com_gsk99_archive_2011_06_14_2080573.html] (substr)

[https://blog.csdn.net/jumtre/article/details/38020389][https_blog.csdn.net_jumtre_article_details_38020389] （instr)

[https://blog.csdn.net/fengyifei11228/article/details/6072431][https_blog.csdn.net_fengyifei11228_article_details_6072431] (length)

[https://www.cnblogs.com/mellowsmile/p/4642306.html][https_www.cnblogs.com_mellowsmile_p_4642306.html] (wm\_concat)

[https://blog.csdn.net/u010825931/article/details/102931661][https_blog.csdn.net_u010825931_article_details_102931661] (to\_number)

[1d880db3d413668622fc668d96fd15e3.png]: /images/20221021/2b0da8a3194e41eba8ba25c89e910bdb.png
[https_www.cnblogs.com_sumsen_archive_2012_05_30_2525767.html]: https://www.cnblogs.com/sumsen/archive/2012/05/30/2525767.html
[https_www.cnblogs.com_gsk99_archive_2011_06_14_2080573.html]: https://www.cnblogs.com/gsk99/archive/2011/06/14/2080573.html
[https_blog.csdn.net_jumtre_article_details_38020389]: https://blog.csdn.net/jumtre/article/details/38020389
[https_blog.csdn.net_fengyifei11228_article_details_6072431]: https://blog.csdn.net/fengyifei11228/article/details/6072431
[https_www.cnblogs.com_mellowsmile_p_4642306.html]: https://www.cnblogs.com/mellowsmile/p/4642306.html
[https_blog.csdn.net_u010825931_article_details_102931661]: https://blog.csdn.net/u010825931/article/details/102931661