Sql Server 字符串、日期函数
来源：互联网 发布：网站排名优化软件 编辑：程序博客网 时间：2024/05/17 20:02
/**//***************************************************************************
DATEPART
返回代表指定日期的指定日期部分的整数。
语法
DATEPART ( datepart , date )
参数
datepart
是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 缩写
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
Hour hh
minute mi, n
second ss, s
millisecond ms
SELECT DATEPART(dy, getdate())
****************************************************************************/
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
SELECT DATEPART(m, getdate()), DATEPART(d, getdate()), DATEPART(yy, getdate())
SELECT DATEPART(mm, getdate()), DATEPART(dd, getdate()), DATEPART(yyyy, getdate())
SELECT DATENAME(month, getdate()) AS 'Month Name'
USE pubs
SELECT @@DBTS
/**//*******************************************************
DATALENGTH
返回任何表达式所占用的字节数。
语法
DATALENGTH ( expression )
参数
expression
任何类型的表达式。
返回类型
int
NULL 的 DATALENGTH 的结果是 NULL。
*******************************************************/
use pubs
SELECT DATALENGTH(au_lname + ','
+ au_fname),datalength(au_lname),datalength(au_fname)
FROM authors
use northwind
select USER_NAME()
use pubs
SELECT au_lname, au_fname
FROM authors
WHERE SOUNDEX(au_fname) = 'M240'
/**//*********************************************************
STR
由数字数据转换来的字符数据。
语法
STR ( float_expression [ , length [ , decimal ] ] )
参数
float_expression
是带小数点的近似数字 (float) 数据类型的表达式。不要在 STR 函数中将函数或子查询用作 float_expression。
length
是总长度，包括小数点、符号、数字或空格。默认值为 10。
decimal
是小数点右边的位数。
返回类型
char
------------------------
例：
select Cast(123.000045656 as float)
select str(123.000045656,10,7)
*********************************************************/
SELECT str(job_id) + ' ' +
str(job_lvl) as 'job_id/job_lvl'
FROM employee
/**//******************************************************
聚合函数
*******************************************************/
SELECT SUM(price) total_price
FROM titles
SELECT count(*)-count(price) total_price
FROM titles
use northwind
SELECT unitprice,(unitprice * .9) as 九折
FROM products
use pubs
SELECT (lname + ', ' + fname) as name
FROM employee
--------///////////////////////////////////////////////////////////
use pubs
SELECT sales.qty, titles.price
FROM sales INNER JOIN titles ON
sales.title_id = titles.title_id
ORDER BY
(sales.qty * titles.price)
select sales.qty,titles.price
from sales,titles where
sales.title_id=titles.title_id
order by sales.qty * titles.price
------///////////////////////////////////////////////////////////
/**//************************************************
字符串截取
语法
SUBSTRING ( expression , start , length )
expression：要截取的字符串或字段
start：开始位置
length：截取子串长度
************************************************/
use pubs
SELECT au_lname, au_fname,phone
FROM authors
WHERE
(SUBSTRING(phone, 1, 3) = '415')
/**//*************************************************
日期函数
*************************************************/
use pubs
SELECT ord_num, ord_date
FROM sales
WHERE
(ord_date >=
DATEADD(day, -10, GETDATE()))
select GETUTCDATE()
/**//***********************************************
ROUND
返回数字表达式并四舍五入为指定的长度或精度
***********************************************/
use northwind
SELECT ProductID, ProductName,
CASE CategoryID
WHEN 1 THEN ROUND( (UnitPrice * .6), 2)
WHEN 2 THEN ROUND( (UnitPrice * .7), 2)
WHEN 3 THEN ROUND( (UnitPrice * .8), 2)
ELSE ROUND( (UnitPrice * .9), 2)
END AS DiscountPrice
FROM Products
use northwind
SELECT ( CAST(ProductID AS VARCHAR(10)) + ': '
+ ProductName ) AS ProductIDName
FROM Products
SELECT Prd.ProductID, Prd.ProductName,
( SELECT SUM(OD.UnitPrice * OD.Quantity)
FROM Northwind.dbo.[Order Details] AS OD
WHERE OD.ProductID = Prd.ProductID
) AS SumOfSales
FROM Northwind.dbo.Products AS Prd
ORDER BY Prd.ProductID
/**//***********************************************
运算符号 + - * / ......
***********************************************/
SELECT ProductID, ProductName,
UnitPrice * UnitsInStock AS InventoryValue
FROM Northwind.dbo.Products
/**//*
FLOOR
返回小于或等于所给数字表达式的最大整数。
RAND
返回 0 到1 之间的随机float 值。
CAST 和 CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
*/
DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT cast(floor(RAND(@counter)*@counter+1) as int) Random_Number
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)
/**//************************************************
SIGN
返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。
************************************************/
DECLARE @value real
SET @value = -1
WHILE @value < 2
BEGIN
SELECT SIGN(@value)
SET NOCOUNT ON
SELECT @value = @value + 1
SET NOCOUNT OFF
END
SET NOCOUNT OFF
GO
/**//***********************************************
CEILING
返回大于或等于所给数字表达式的最小整数。
************************************************/
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)
/**//***********************************************
PATINDEX
返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
语法
PATINDEX ( '%pattern%' , expression )
参数
pattern
一个字符串。可以使用通配符，但 pattern 之前和之后必须有 % 字符（搜索第一个和最后一个字符时除外）。pattern 是短字符数据类型类别的表达式。
expression
一个表达式，通常为要在其中搜索指定模式的列，expression 为字符串数据类型类别。
返回类型
int
************************************************/
USE pubs
GO
SELECT PATINDEX('%wonderful%', notes)
FROM titles
WHERE title_id = 'TC3218'
GO
USE pubs
GO
SELECT PATINDEX('%won_erful%', notes)
FROM titles
WHERE title_id = 'TC3218'
GO
SELECT PATINDEX('%[A,Z,0-9]%[A,Z,0-9]%[A,Z,0-9]%','XYZABC123')
select patindex('AB%','ABXXXXXXXXX')
/**//***********************************************************************
datalength
数据长度
REVERSE
返回字符表达式的反转。
************************************************************************/
select datalength('Profusely illustrated in color, this makes a w')
SELECT PATINDEX(REVERSE('%wonberful%'),REVERSE('Profusely illustrated in color, this makes a wonderful gift book wonberful for a cuisine-oriented friend.'))
select REVERSE('Profusely illustrated in color, this makes a wonderful gift book wonberful for a cuisine-oriented friend.')
select * from pubs.dbo.titles
/**//************************************************************************
SPACE
返回由重复的空格组成的字符串。
语法
SPACE ( integer_expression )
参数
integer_expression
是表示空格个数的正整数。如果 integer_expression 为负，则返回空字符串。
返回类型
char
****************************************
RTRIM
截断所有尾随空格后返回一个字符串。
LTRIM
删除起始空格后返回字符表达式。
*************************************************************************/
USE pubs
GO
SELECT au_lname,au_fname,RTRIM(au_lname) + ',' + SPACE(2) + LTRIM(au_fname),au_lname+', '+au_fname
FROM authors
ORDER BY au_lname, au_fname
GO
/**//************************************************************************
CHARINDEX
返回字符串中指定表达式的起始位置。
*************************************************************************/
USE pubs
GO
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
GO
/**//************************************************************************
LEN
返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。
DATALENGTH
返回任何表达式所占用的字节数。
（一个字母数字占1个字节，一个汉字占两个字节）
*************************************************************************/
select datalength('Profusely illustrated in color, this makes a w')
--46个字节
select len('Profusely illustrated in color, this makes a w')
--46个字符
select datalength('返回字符串中指定表达式的起始位置')
--32个字节
select len('返回字符串中指定表达式的起始位置')
--16个字符
/**//***********************************************************************
REPLACE
用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
语法
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
参数
'string_expression1'
待搜索的字符串表达式。string_expression1 可以是字符数据或二进制数据。
'string_expression2'
待查找的字符串表达式。string_expression2 可以是字符数据或二进制数据。
'string_expression3'
替换用的字符串表达式。string_expression3 可以是字符数据或二进制数据。
************************************************************************/
--示例
--下例用 xxx 替换 abcdefghi 中的字符串 cde。
SELECT REPLACE('abcdefghicde','cde','xxx')
/**//***********************************************************************
STR
由数字数据转换来的字符数据。
语法
STR ( float_expression [ , length [ , decimal ] ] )
参数
float_expression
是带小数点的近似数字 (float) 数据类型的表达式。不要在 STR 函数中将函数或子查询用作 float_expression。
length
是总长度，包括小数点、符号、数字或空格。默认值为 10。
decimal
是小数点右边的位数。
返回类型
char
************************************************************************/
SELECT STR(123.45, 6, 1)
/**//************************************************************************
DIFFERENCE
以整数返回两个字符表达式的 SOUNDEX 值之差。
*************************************************************************/
USE pubs
GO
-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'),
SOUNDEX('Greene'), DIFFERENCE('Green','Greene')
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'),
SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene')
GO
/**//**************************************************************************
STUFF
删除指定长度的字符并在指定的起始点插入另一组字符。
语法
STUFF ( character_expression , start , length , character_expression )
参数
character_expression
由字符数据组成的表达式。character_expression 可以是常量、变量，也可以是字符或二进制数据的列。
start
是一个整形值，指定删除和插入的开始位置。如果 start 或 length 是负数，则返回空字符串。如果 start 比第一个 character_expression 长，则返回空字符串。
length
是一个整数，指定要删除的字符数。如果 length 比第一个 character_expression 长，则最多删除到最后一个 character_expression 中的最后一个字符。
返回类型
如果 character_expression 是一个支持的字符数据类型，则返回字符数据。如果 character_expression 是一个支持的 binary 数据类型，则返回二进制数据。
注释
可以嵌套字符串函数。
***************************************************************************/
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
--下面是结果集：aijklmnef
--开始位置是2 删除3个字符 即'abc' 在第二个位置插入'ijklmn'
/**//**************************************************************************
REPLICATE
以指定的次数重复字符表达式。
***************************************************************************/
--A. 使用 REPLICATE
--下例重复两遍每个作者的名字。
USE pubs
SELECT REPLICATE(au_fname, 2)
FROM authors
ORDER BY au_fname
--B. 使用 REPLICATE、SUBSTRING 和 SPACE
--下例使用 REPLICATE、SUBSTRING 和 SPACE 生成 authors 表中的所有作者的电话和传真列表。
-- Replicate phone number twice because the fax number is identical to
-- the author telephone number.
USE pubs
GO
SELECT (UPPER(au_lname) + ',' + SPACE(1) + au_fname) as AllName,SUBSTRING((UPPER(au_lname) + ',' + SPACE(1) + au_fname), 1, 35)
AS Name, phone AS Phone, REPLICATE(phone,1) AS Fax
FROM authors
ORDER BY au_lname, au_fname
GO
--C. 使用 REPLICATE 和 DATALENGTH
--本例中，当数值从数字数据类型转换为字符型或 Unicode 型时，从左填充数字，使其达到指定的长度。
--char 和 varchar
--固定长度 (char) 或可变长度 (varchar) 字符数据类型。
USE Northwind
GO
DROP TABLE t1
GO
CREATE TABLE t1
(
c1 varchar(3),
c2 char(3)
)
GO
INSERT INTO t1 VALUES ('2', '2')
INSERT INTO t1 VALUES ('37', '37')
INSERT INTO t1 VALUES ('597', '597')
GO
SELECT DATALENGTH(c1) as tempC1,REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS [Varchar Column],
DATALENGTH(c2) as tempC2,REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS [Char Column]
FROM t1
GO
/**//*********************************************************************************************
SUBSTRING
字符串截取:字符串expression 从指定的位置(start) 截取指定长度(length)的字符串
语法
SUBSTRING ( expression , start , length )
参数
expression
是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。
start
是一个整数，指定子串的开始位置。
length
是一个整数，指定子串的长度（要返回的字符数或字节数）。
*********************************************************************************************/
--A. 在字符串上使用 SUBSTRING
USE pubs
SELECT au_lname,au_fname, SUBSTRING(au_fname, 1, 1)
FROM authors
ORDER BY au_lname
--B. 在 text、ntext 和 image 数据上使用 SUBSTRING
USE pubs
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo,
SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = '1756'
/**//*******************************************************************************************
REVERSE
返回字符表达式的反转。
********************************************************************************************/
USE pubs
GO
SELECT au_fname,REVERSE(au_fname)
FROM authors
ORDER BY au_fname
GO
/**//*******************************************************************************************
LOWER
将大写字符数据转换为小写字符数据后返回字符表达式。
UPPER
返回将小写字符数据转换为大写的字符表达式。
LEFT
返回从字符串左边开始指定个数的字符。
RIGHT
返回字符串中从右边开始指定个数的 integer_expression 字符。
LTRIM
删除起始空格后返回字符表达式。
RTRIM
截断所有尾随空格后返回一个字符串。
********************************************************************************************/
SELECT title,LOWER(SUBSTRING(title, 1, 20)) AS Lower,
UPPER(SUBSTRING(title, 1, 20)) AS Upper,
LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpper
FROM titles
WHERE price between 11.00 and 20.00
GO
USE pubs
GO
SELECT au_lname,UPPER(RTRIM(au_lname)) + ', ' + ltrim(au_fname) AS Name
FROM authors
ORDER BY au_lname
GO
USE pubs
GO
SELECT au_fname,RIGHT(au_fname, 5)
FROM authors
ORDER BY au_fname
GO
DECLARE @string_to_trim varchar(60)
SET @string_to_trim = ' Five spaces are at the beginning of this
string.'
SELECT 'Here is the string without the leading spaces: ' +
LTRIM(@string_to_trim) as nospace,'Here is the string without the leading spaces: ' +
@string_to_trim as isspace
GO
DECLARE @string_to_trim varchar(60)
SET @string_to_trim = 'Four spaces fgfg. '
SELECT 'Here: ' + CHAR(13) +
RTRIM(@string_to_trim)
GO
