注册 | 登录
欢迎注册会员

当前位置:首页 > 数据库 > MSSQL数据库 > 正文

sql 处理字符串的函数总结

来源:程序人生 【 】 浏览:1841 添加日期:2010-08-19 09:11:36 我要评论(0)
1.绝对值?
SQL:select?abs(-1)?value?
O:select?abs(-1)?value?from?dual?
2.取整(大)?
S:select?ceiling(-1.001)?value?
O:select?ceil(-1.001)?value?from?dual?
3.取整(小)?
S:select?floor(-1.001)?value?
O:select?floor(-1.001)?value?from?dual?
4.取整(截取)?
S:select?cast(-1.002?as?int)?value?
O:select?trunc(-1.002)?value?from?dual?
5.四舍五入?
S:select?round(1.23456,4)?value?1.23460?
O:select?round(1.23456,4)?value?from?dual?1.2346?
6.e为底的幂?
S:select?Exp(1)?value?2.7182818284590451?
O:select?Exp(1)?value?from?dual?2.71828182?
7.取e为底的对数?
S:select?log(2.7182818284590451)?value?1?
O:select?ln(2.7182818284590451)?value?from?dual;?1?
8.取10为底对数?
S:select?log10(10)?value?1?
O:select?log(10,10)?value?from?dual;?1?
9.取平方?
S:select?SQUARE(4)?value?16?
O:select?power(4,2)?value?from?dual?16?
10.取平方根?
S:select?SQRT(4)?value?2?
O:select?SQRT(4)?value?from?dual?2?
11.求任意数为底的幂?
S:select?power(3,4)?value?81?
O:select?power(3,4)?value?from?dual?81?
12.取随机数?
S:select?rand()?value?
O:select?sys.dbms_random.value(0,1)?value?from?dual;?
13.取符号?
S:select?sign(-8)?value?-1?
O:select?sign(-8)?value?from?dual?-1?
----------数学函数?
14.圆周率?
S:SELECT?PI()?value?3.1415926535897931?
O:不知道?
15.sin,cos,tan?参数都以弧度为单位?
例如:select?sin(PI()/2)?value?得到1(SQLServer)?
16.Asin,Acos,Atan,Atan2?返回弧度?
17.弧度角度互换(SQLServer,Oracle不知道)?
DEGREES:弧度-〉角度?
RADIANS:角度-〉弧度?
---------数值间比较?
18.?求集合最大值?
S:select?max(value)?value?from?
(select?1?value?
union?
select?-2?value?
union?
select?4?value?
union?
select?3?value)a?
O:select?greatest(1,-2,4,3)?value?from?dual?
19.?求集合最小值?
S:select?min(value)?value?from?
(select?1?value?
union?
select?-2?value?
union?
select?4?value?
union?
select?3?value)a?
O:select?least(1,-2,4,3)?value?from?dual?
20.如何处理null值(F2中的null以10代替)?
S:select?F1,IsNull(F2,10)?value?from?Tbl?
O:select?F1,nvl(F2,10)?value?from?Tbl?
--------数值间比较?
21.求字符序号?
S:select?ascii('a')?value?
O:select?ascii('a')?value?from?dual?
22.从序号求字符?
S:select?char(97)?value?
O:select?chr(97)?value?from?dual?
23.连接?
S:select?'11'+'22'+'33'?value?
O:select?CONCAT('11','22')||33?value?from?dual?
23.子串位置?--返回3?
S:select?CHARINDEX('s','sdsq',2)?value?
O:select?INSTR('sdsq','s',2)?value?from?dual?
23.模糊子串的位置?--返回2,参数去掉中间%则返回7?
S:select?patindex('%d%q%','sdsfasdqe')?value?
O:oracle没发现,但是instr可以通过第四霾问?刂瞥鱿执问?BR>?select?INSTR('sdsfasdqe','sd',1,2)?value?from?dual?返回6?
24.求子串?
S:select?substring('abcd',2,2)?value?
O:select?substr('abcd',2,2)?value?from?dual?
25.子串代替?返回aijklmnef?
S:SELECT?STUFF('abcdef',?2,?3,?'ijklmn')?value?
O:SELECT?Replace('abcdef',?'bcd',?'ijklmn')?value?from?dual?
26.子串全部替换?
S:没发现?
O:select?Translate('fasdbfasegas','fa','我'?)?value?from?dual?
27.长度?
S:len,datalength?
O:length?
28.大小写转换?lower,upper?
29.单词首字母大写?
S:没发现?
O:select?INITCAP('abcd?dsaf?df')?value?from?dual?
30.左补空格(LPAD的第一个参数为空格则同space函数)?
S:select?space(10)+'abcd'?value?
O:select?LPAD('abcd',14)?value?from?dual?
31.右补空格(RPAD的第一个参数为空格则同space函数)?
S:select?'abcd'+space(10)?value?
O:select?RPAD('abcd',14)?value?from?dual?
32.删除空格?
S:ltrim,rtrim?
O:ltrim,rtrim,trim?
33.?重复字符串?
S:select?REPLICATE('abcd',2)?value?
O:没发现?
34.发音相似性比较(这两个单词返回值一样,发音相同)?
S:SELECT?SOUNDEX?('Smith'),?SOUNDEX?('Smythe')?
O:SELECT?SOUNDEX?('Smith'),?SOUNDEX?('Smythe')?from?dual?
SQLServer中用SELECT?DIFFERENCE('Smithers',?'Smythers')?比较soundex的差?
返回0-4,4为同音,1最高?
--------------日期函数?
35.系统时间?
S:select?getdate()?value?
O:select?sysdate?value?from?dual?
36.前后几日?
直接与整数相加减?
37.求日期?
S:select?convert(char(10),getdate(),20)?value?
O:select?trunc(sysdate)?value?from?dual?
select?to_char(sysdate,'yyyy-mm-dd')?value?from?dual?
38.求时间?
S:select?convert(char(8),getdate(),108)?value?
O:select?to_char(sysdate,'hh24:mm:ss')?value?from?dual?
39.取日期时间的其他部分?
S:DATEPART?和?DATENAME?函数?(第一个参数决定)?
O:to_char函数?第二个参数决定?
参数---------------------------------下表需要补充?
year?yy,?yyyy?
quarter?qq,?q?(季度)?
month?mm,?m?(m?O无效)?
dayofyear?dy,?y?(O表星期)?
day?dd,?d?(d?O无效)?
week?wk,?ww?(wk?O无效)?
weekday?dw?(O不清楚)?
Hour?hh,hh12,hh24?(hh12,hh24?S无效)?
minute?mi,?n?(n?O无效)?
second?ss,?s?(s?O无效)?
millisecond?ms?(O无效)?
----------------------------------------------?
40.当月最后一天?
S:不知道?
O:select?LAST_DAY(sysdate)?value?from?dual?
41.本星期的某一天(比如星期日)?
S:不知道?
O:SELECT?Next_day(sysdate,7)?vaule?FROM?DUAL;?
42.字符串转时间?
S:可以直接转或者select?cast('2004-09-08'as?datetime)?value?
O:SELECT?To_date('2004-01-05?22:09:38','yyyy-mm-dd?hh24-mi-ss')?vaule?FROM?DUAL;?
43.求两日期某一部分的差(比如秒)?
S:select?datediff(ss,getdate(),getdate()+12.3)?value?
O:直接用两个日期相减(比如d1-d2=12.3)?
SELECT?(d1-d2)*24*60*60?vaule?FROM?DUAL;?
44.根据差值求新的日期(比如分钟)?
S:select?dateadd(mi,8,getdate())?value?
O:SELECT?sysdate+8/60/24?vaule?FROM?DUAL;?
45.求不同时区时间?
S:不知道?
O:SELECT?New_time(sysdate,'ydt','gmt'?)?vaule?FROM?DUAL;?
-----时区参数,北京在东8区应该是Ydt-------?
AST?ADT?大西洋标准时间?
BST?BDT?白令海标准时间?
CST?CDT?中部标准时间?
EST?EDT?东部标准时间?
GMT?格林尼治标准时间?
HST?HDT?阿拉斯加—夏威夷标准时间?
MST?MDT?山区标准时间?
NST?纽芬兰标准时间?
PST?PDT?太平洋标准时间?
YST?YDT?YUKON标准时间?
Oracle支持的字符函数和它们的Microsoft?SQL?Server等价函数:?
函数?Oracle?Microsoft?SQL?Server?
把字符转换为ASCII?ASCII?ASCII?
字串连接?CONCAT?(expression?+?expression)?
把ASCII转换为字符?CHR?CHAR?
返回字符串中的开始字符(左起)?INSTR?CHARINDEX?
把字符转换为小写?LOWER?LOWER?
把字符转换为大写?UPPER?UPPER?
填充字符串的左边?LPAD?N/A?
清除开始的空白?LTRIM?LTRIM?
清除尾部的空白?RTRIM?RTRIM?
字符串中的起始模式(pattern)?INSTR?PATINDEX?
多次重复字符串?RPAD?REPLICATE?
字符串的语音表示?SOUNDEX?SOUNDEX?
重复空格的字串?RPAD?SPACE?
从数字数据转换为字符数据?TO_CHAR?STR?
子串?SUBSTR?SUBSTRING?
替换字符?REPLACE?STUFF?
将字串中的每个词首字母大写?INITCAP?N/A?
翻译字符串?TRANSLATE?N/A?
字符串长度?LENGTH?DATELENGTH?or?LEN?
列表中最大的字符串?GREATEST?N/A?
列表中最小的字符串?LEAST?N/A?
如果为NULL则转换字串?NVL?ISNULL?

日期函数?
函数?Oracle?Microsoft?SQL?Server?
日期相加?(date?column?+/-?value)?or?
ADD_MONTHS?DATEADD?
两个日期的差?(date?column?+/-?value)?or?
MONTHS_BETWEEN?DATEDIFF?
当前日期和时间?SYSDATE?GETDATE()?
一个月的最后一天?LAST_DAY?N/A?
时区转换?NEW_TIME?N/A?
日期后的第一个周日?NEXT_DAY?N/A?
代表日期的字符串?TO_CHAR?DATENAME?
代表日期的整数?TO_NUMBER?
(TO_CHAR))?DATEPART?
日期舍入?ROUND?CONVERT?
日期截断?TRUNC?CONVERT?
字符串转换为日期?TO_DATE?CONVERT?
如果为NULL则转换日期?NVL?ISNULL?

转换函数?
函数?Oracle?Microsoft?SQL?Server?
数字转换为字符?TO_CHAR?CONVERT?
字符转换为数字?TO_NUMBER?CONVERT?
日期转换为字符?TO_CHAR?CONVERT?
字符转换为日期?TO_DATE?CONVERT?
16进制转换为2进制?HEX_TO_RAW?CONVERT?
2进制转换为16进制?RAW_TO_HEX?CONVERT?

其它行级别的函数?
函数?Oracle?Microsoft?SQL?Server?
返回第一个非空表达式?DECODE?COALESCE?
当前序列值?CURRVAL?N/A?
下一个序列值?NEXTVAL?N/A?
如果exp1?=?exp2,?返回null?DECODE?NULLIF?
用户登录账号ID数字?UID?SUSER_ID?
用户登录名?USER?SUSER_NAME?
用户数据库ID数字?UID?USER_ID?
用户数据库名?USER?USER_NAME?
当前用户?CURRENT_USER?CURRENT_USER?
用户环境(audit?trail)?USERENV?N/A?
在CONNECT?BY子句中的级别?LEVEL?N/A?

合计函数?
函数?Oracle?Microsoft?SQL?Server?
Average?AVG?AVG?
Count?COUNT?COUNT?
Maximum?MAX?MAX?
Minimum?MIN?MIN?
Standard?deviation?STDDEV?STDEV?or?STDEVP?
Summation?SUM?SUM?
Variance?VARIANCE?VAR?or?VARP?
Oracle还有一个有用的函数EXTRACT,提取并且返回日期时间或时间间隔表达式中特定的时间域:?
EXTRACT(YEAR?FROM?日期)

你浏览的文章是 - 《sql 处理字符串的函数总结》!
文章出处:https://www.procedurelife.com/content/akkhek.html
0% (0)
0% (0)
评论0
头像 游客
1 2