读《程序员的SQL金典》[2]--函数www.129028.com金沙:

日期:2020-04-29编辑作者:数据库

SQL SERVER支持的字符串函数内容:

一、数学函数


1.RAND

SELECT RAND () ---0.302870228294199

取0-1之间的随机小数。 

2.小数取整

  • CEILINT(data)舍掉小数部分并向上取整。
  • FLOOR(data)舍掉小数部分并向下取整。

    SELECT TOP 3 FWeight, CEILING(FWeight ),FLOOR( FWeight) FROM T_Person

  • Round(m,d):四舍五入对小数进行取整。

参数说明:m为带处理的数据,d为四舍五入时保留的小数位数,为负数时表示对整数进行四舍五入。

SELECT TOP 3 FWeight, ROUND(FWeight ,2), ROUND(FWeight ,-1) FROM T_Person

结果:

FWeight     (无列名)     (无列名)
56.67     56.67     60.00
36.17     36.17     40.00
40.33     40.33     40.00

3.SIGN():求符号

SELECT FWeight ,SIGN( FWeight-50 )as '>50' FROM T_Person

结果:

FWeight     >50
56.67     1.00
36.17     -1.00

4.取余:

在SQL Server中,使用百分号%来实现取余操作。

SELECT FWeight ,FWeight% 10 FROM T_Person

结果:

FWeight     (无列名)
56.67     6.67
36.17     6.17
40.33     0.33

复制代码 代码如下:LEN(string)函数LOWER(string)函数UPPER (string)函数LTRIM(string)函数RTRIM (string)函数SUBSTRING(string,start_position,length)函数CHARINDEX(string,substring)函数LEFT (string,length)函数RIGHT (string,length) 函数ASCII(string)函数ASCII(string)函数

二、字符串函数

1.LEN(str):计算字符串长度。

2.去除空格

  • LTRIM(str):去掉字符串左侧空格。
  • RTRIM(str):去掉字符串右侧空格。

    SELECT ' HELLO ' ,LEN( ' HELLO '),LEN (LTRIM( ' HELLO ')),LEN (RTRIM( LTRIM(' HELLO ' )))

结果:

(无列名)     (无列名)     (无列名)     (无列名)
HELLO             7                5                5

3.截取子串

  • SUBSTRING(str,start,length):取子字符串。其中start从1开始计算,length为子串长度。
  • LEFT(str,length):从最左侧开始截取子串。
  • RIGHT(str,length):从最右侧开始截取子串。

    SELECT FName ,SUBSTRING( FName,2 ,2),LEFT( FName,2 ) as l,RIGHT( FName,2 ) as r FROM T_Person

结果:

FName     (无列名)     l     r
Lily     il     Li     ly
Kelly     el     Ke     ly
Sam     am     Sa     am

4.字符串替换

REPLACE(str,old,new):str为要替换的原字符串,old为要被替换的字符串部分,new是用来替换的新字符串。

SELECT LEN (' HELLO '), LEN(REPLACE (' HELLO ', ' ','' )) --结果6,5

5.ASCII码

  • ASCII(char)计算字符的ASCII码,如果参数是一个字符串,则返回第一个字母的ASCII码。
  • CHAR(num)正好相反,是根据ASCII码数值计算对应的字符。

    SELECT ASCII ('A'), ASCII('ANY' ),CHAR( 90)--65,65,Z

6.发音匹配度 DIFFERENCE

在SQL Server中使用DIFFERENCE方法查询两个字符串的发音相似度。该方法计算两个字符串的发音特征值并进行比较,然后返回一个0-4之间的整数,这个数字越大表示两个字符串发音越相似。

例如:

SELECT FName FROM T_Person WHERE DIFFERENCE( FName,'Tony' )>2

三、日期时间函数

1.GETDATE()--当前时间

SQL Server中取得当前时间的函数GETDATE(),可以使用CONVERT函数对其格式进行处理。

SELECT GETDATE () --2014-02-17 12:44:39.933 
SELECT CONVERT (VARCHAR( 50),GETDATE (),101) AS NOWDATE --02/17/2014 
SELECT CONVERT (VARCHAR( 50),GETDATE (),108) AS NOWDATE --12:44:39

2.DATEADD()--日期增减

DATEADD(datepart,number,date):date为要计算的日期,number为加减日期数,datepart可以理解为加减的单位。

www.129028.com金沙 1

例如:

SELECT FBirthDay ,DATEADD( YEAR,1 ,FBirthDay) FROM T_Person 
SELECT FBirthDay ,DATEADD( QUARTER,2 ,FBirthDay) FROM T_Person 
SELECT FBirthDay ,DATEADD( WEEK,-2 ,FBirthDay) FROM T_Person

3.DATEDIFF()--日期差额

DATEDIFF(datedepart,startdate,enddate):其中datedepart含义同DATEADD参数,startdate和enddate为相减的两个日期。

SELECT FBirthDay ,FRegDay, DATEDIFF(YEAR ,FBirthDay, FRegDay) AS UserAge FROM T_Person

结果:

FBirthDay                                  FRegDay                              UserAge
1981-03-22 00:00:00.000     1998-05-01 00:00:00.000     17
1987-01-18 00:00:00.000     1999-08-21 00:00:00.000     12
1987-11-08 00:00:00.000     2001-09-18 00:00:00.000     14

4.DATENAME()--计算日期名称

DATENAME(datedepart,date):datedepart参数同DATEADD,date为要计算的目标日期。

SELECT GETDATE () AS NOW, DATENAME(YEAR ,GETDATE()) AS YEAR,DATENAME (DAY, GETDATE()) AS 日期 ,DATENAME( WEEKDAY,GETDATE ()) AS WEEKDAY,DATENAME (QUARTER, GETDATE()) AS 季度

结果:

NOW                                        YEAR     日期     WEEKDAY     季度
2014-02-17 13:57:56.127     2014     17        星期一               1

5.DATEPART()--取得日期指定部分

DATEPART(datedepart,date):datedepart参数同DATEADD,date为要计算的目标日期。该方法和DATENAME类似,不过DATEPART返回值是数字,而DATENAME尽量返回的名称。

SELECT GETDATE () AS NOW, DATEPART(YEAR ,GETDATE()) AS YEAR,DATEPART (DAY, GETDATE()) AS 日期 ,DATEPART( WEEKDAY,GETDATE ()) AS WEEKDAY, DATEPART(QUARTER ,GETDATE()) AS 季度

LEN(string)函数:此函数是用来计算一个字符串的长度,接受一个参数。这里面,是没有区分大小写。LEN,len,还是Len都等同。例子如下:

四、其他函数

1. 类型转换

  • CAST(exp AS datetype)
  • CONVERT(datetype,exp)

    SELECT FIdNumber ,CAST(RIGHT( FIdNumber,4 ) AS INT), Convert(INT ,RIGHT(FIdNumber, 4))%2 FROM T_Person

结果:

FIdNumber     (无列名)     (无列名)
123456789120     9120     0
123456789121     9121     1
123456789122     9122     0

2.空值处理

①COALESCE(exp,val1,val2....):参数个数不固定。如果exp为null则返回val1,如果val1为null则返回val2...以此类推。如果所有值都为null,则返回null。

例如:

SELECT FBirthDay ,FRegDay, COALESCE(FBirthDay ,FRegDay, '1900-1-1') FROM T_Person

结果:

FBirthDay                                 FRegDay                                  (无列名)
1982-07-12 00:00:00.000     2000-03-01 00:00:00.000     1982-07-12 00:00:00.000
1983-02-16 00:00:00.000     1998-05-01 00:00:00.000     1983-02-16 00:00:00.000
NULL                                        1999-03-01 00:00:00.000     1999-03-01 00:00:00.000
NULL                                        NULL                                         1900-01-01 00:00:00.000
1972-07-18 00:00:00.000     1995-06-19 00:00:00.000     1972-07-18 00:00:00.000

②ISNULL(exp1,exp2):如果exp1值为NULL,则返回exp2.

例如:

SELECT FBirthDay ,FRegDay, ISNULL(FBirthDay ,FRegDay) FROM T_Person

结果:

FBirthDay                                FRegDay                                      (无列名)
1982-07-12 00:00:00.000     2000-03-01 00:00:00.000     1982-07-12 00:00:00.000
1983-02-16 00:00:00.000     1998-05-01 00:00:00.000     1983-02-16 00:00:00.000
NULL                                         1999-03-01 00:00:00.000     1999-03-01 00:00:00.000
NULL                                         NULL                                         NULL
1972-07-18 00:00:00.000     1995-06-19 00:00:00.000     1972-07-18 00:00:00.000

③NULLIF(exp1,exp2):

如果exp1等于exp2,则返回NULL;否则返回exp1.

实例:

SELECT NULLIF (1, 1),NULLIF (1, 2),NULLIF (1,NULL)  --返回NULL,1,1

3.SQL Server独有函数

①PATINDEX()--可以使用通配符模糊查询某字符串出现的位置,功能比CHARINDEX强大。

例如:

SELECT FName ,PATINDEX( '%_i%',FName ) FROM T_Person

结果:

www.129028.com金沙 2

②REPLICATE(str,count):将一个字符串str重复count次。

SPACE(N):将空格重复N次。

例如:

SELECT FName ,REPLICATE( FName,3 ),FNAME+ SPACE(5 )+FNAME FROM T_Person

www.129028.com金沙 3

③REVERSE(str):返回字符串的倒序。

④APP_NAME():当前应用程序名称;HOST_NAME():返回工作站名;CURRENT_USER:返回当前登录用户名。

SELECT FName, LEN(FName) FROM T_Person注:如果给LEN函数传递的参数是一个时间字段的话,那么返回的结果就不对,比如一个DateTime。所以呢,这个函数是用来计算字符串的长度,别的类型参数也能返回结果,但是就不一定正确。

I、如果传进去的是NULL,那么返回出来的还是NULL。

II、对于一个字符串形如' a a ',返回的结果是4,而不是5。即计算的结果不包括右侧全为空格字符串部分。

LOWER(string)函数:此函数是用来把一字符串都转换为小写字符串。跟LEN函数一样,也接受一个参数。复制代码 代码如下:SELECT FName, LOWER(FName) FROM T_Person

www.129028.com金沙,注:如果传进去的是NULL,那么返回出来的还是NULL。

UPPER (string)函数:与LOWER()函数相反,此函数把字符串都转换为大写字符串。也同样接受一个参数。复制代码 代码如下:SELECT FName, UPPER(FName) FROM T_Person

注:如果传进去的是NULL,那么返回出来的还是NULL。

LTRIM(string)函数:此函数是去除字符串左边的空格。也同样接受一个参数。复制代码 代码如下:SELECT FName,LTRIM(FName),LTRIM(' abc ') FROM T_Person

注:如果传进去的是NULL,那么返回出来的还是NULL。

RTRIM (string)函数:此函数是去除字符串右边的空格。也同样接受一个参数。复制代码 代码如下:SELECT FName,RTRIM(FName),RTRIM(' abc ') FROM T_Person

注:如果传进去的是NULL,那么返回出来的还是NULL。

如果需要去除两边的空格,则需要联合来使用。复制代码 代码如下:SELECT FName,LTRIM(RTRIM(FName)),LTRIM(RTRIM(' abc ')) FROMT_Person

SUBSTRING(string,start_position,length)函数:此函数是使用来获取子字符串。其中参数string为主字符串,start_position为子字符串在主字符串中的起始位置,length为子字符串的最大长度。需要注意的是这里的start_position是从1开始,不同于数组是从0开始。如果给的是0的话,相当于取了一个''。复制代码 代码如下:SELECT FName, SUBSTRING(FName,2,3) FROM T_Person

本文由www.129028.com金沙发布于数据库,转载请注明出处:读《程序员的SQL金典》[2]--函数www.129028.com金沙:

关键词:

SQL Server 2000的安全配置

SQLserver2000数据库应用非常广泛,一旦出现安全问题,造成的损失往往难以估量!应提前预防,防患于未然。这里,我...

详细>>

如何查看某个查询用了多少TempDB空间www.129028.com金沙

DECLARE @read BIGINT, @write BIGINT; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBFJOI...

详细>>

浅析SQL语句中GROUP BY的用法

GROUP BY 是分组查询, 一般 GROUP BY 是和 聚合函数配合使用,你可以想想 你用了GROUP BY 按 ITEM.ITEMNUM这个字段分组,那其他字...

详细>>

微软总是试图使它的软件安装尽可能地简单顺www.129028.com金沙

如果你没有特意记录安装日期,那么有没有办法从SQL Server里查询到呢?想想我们在安装的时候,肯定会有Windows认证登...

详细>>