Sql Server 字符串、日期函数 收藏

 

/**//***************************************************************************
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

 

 

---------------------

 

 

declare @t table(id int,year int,  month int, day int,hours int, minutes int, seconds float)
insert @t select 1,2009,12,26,23,13,4.5

select
  id,
  ltrim([year])+'-'+right('00'+ltrim([month]),2)+'-'+right('00'+ltrim([day]),2)+' ' --年月日
+right('00'+ltrim(hours),2)+':'+right('00'+ltrim(Minutes),2)+':'+right('00'+ltrim(cast([Seconds] as dec(18,0))),2)+' ' --时分秒
+right('000'+ltrim(cast([Seconds]*1000 as dec(18,0))%1000),3)  --毫秒
as [date]
from
@t

/**
id          date
----------- --------------------------------------------
1           2009-12-26 23:13:05 500

(1 行受影响)
**/