sqlserver中的常见函数用法

今日碰到个问题：要实现数据表中的一个字段中的文本为"xxx.gif"的转换为"xxx.jpg",我不知道其具体名称，只知道是以gif结尾。
问题解决：update pet set petPhoto=substring(petPhoto,1,datalength(petPhoto)-3)+'jpg'
                       where petPhoto like '%.gif'
注意匹配符：“%”为匹配任意长度任意字符,“_”匹配单个任意字符，[A]匹配以A开头的，[^A]匹配除开以A开头的。知道函数是解决问题的关键（以下转自网络）：

1，统计函数 avg, count, max, min, sum
2， 数学函数
ceiling(n) 返回大于或者等于n的最小整数
floor(n), 返回小于或者是等于n的最大整数
round(m,n), 四舍五入,n是保留小数的位数
abs(n) 绝对值
sign(n), 当n>0, 返回1，n=0,返回0，n<0, 返回-1
PI(), 3.1415....
rand(),rand(n), 返回0-1之间的一个随机数
3，字符串函数
ascii(), 将字符转换为ASCII码, ASCII('abc') = 97
char(), ASCII 码 转换为 字符
lower()，upper() 大小写转换
str(a,b,c)转换数字为字符串。 a,是要转换的字符串。b是转换以后的长度，c是小数位数。str(123.456,8,2) = 123.46
ltrim(), rtrim() 去空格 ltrim去左边的空格,rtrim去右边的空格
left(n), right(n), substring(str, start,length) 截取字符串
charindex(子串，母串），查找是否包含。 返回第一次出现的位置，没有返回0
patindex('%pattern%', expression) 功能同上，可是使用通配符
replicate('char', rep_time), 重复字符串
reverse(char),颠倒字符串
replace(str, strold, strnew) 替换字符串
space(n), 产生n个空行
stuff(), SELECT STUFF('abcdef', 2, 3, 'ijklmn') ='aijklmnef', 2是开始位置，3是要从原来串中删除的字符长度，ijlmn是要插入的字符串。
3，类型转换函数:
cast, cast( expression as data_type), Example:
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
convert(data_type, expression)
4,日期函数
day(), month(), year()
dateadd(datepart, number, date), datapart指定对那一部分加，number知道加多少，date指定在谁的基础上加。datepart的取值包括，year,quarter,month,dayofyear,day,week,hour,minute,second,比如明天dateadd(day,1, getdate())
datediff(datepart,date1,date2). datapart和上面一样。整个函数结果是date2 - date1
datename(datepart, date) 取那一部分，返回字符串。
datepart(datepart, date) 取一部分，返回整数。
getdate()当前时间
5，系统函数
col_length('tablename','colname')
col_name, SELECT COL_NAME(OBJECT_ID('Employees'), 1) = EmployeeID
datalength, example: datalenght('abc') =3, datalength(pub_name) --列名
len,
for xml path('') 转换成xml值，并去除根节点

备注说明:
Len函数：

计算字符串长度的函数，这个函数与oracle和db2函数不一样，那两个数据库用的是length，而sqlserver用的是LEN
select len('aaa') as length

ascii函数：

这个函数返回字符串中最左侧的字符的ASCII 码，如果是一个字符的话，那就返回该字符的ASCII 码，如果是多个字符的话，那就返回最左边字符的ASCII 码
select ascii('a') as ascii

charindex函数：

查找字符串的位置：

语法为：charindex(字符串表达式 1, 字符串表达式2，[整数表达式])

该函数用法为：在字符串2 中查找字符串 1，如果存在就返回第一个匹配的位置，如果不存在返回0。如果字符串 1 和字符串 2 中有一个是null 则返回 null。后面的整数为字符串2 中查找的起始位置
select charindex('zain','zhuzainming') as chariindexs

left和right函数

语法为：left(str,index),在字符串str中返回左边index个字符，

right(str,index),在字符串str中返回右边index个字符

select left('zainzhu',2) as charindexs

select right('zainzhu',2) as charindexs

lower和upper函数

该函数主要是返回字符串的大小写，这两个函数与db2和oracle数据库函数的使用一样。
select lower('zainzhu') as char
select upper('zainzhu') as char

ltrim和rtrim函数：

ltrim函数去掉左边空格，rtrim函数去掉右边空格。但ms sqlserver没有trim函数，这个与db2和oracle还是有点不一样的
select ltrim(' zainzhu') as char
select rtrim('zainzhu ') as char
select 'zainzhu '

reverse函数：
这个函数是反转函数，就是将函数按照逆序显示出来。
select reverse('zainzhu')

字符串连接：
sqlserver字符串连接是用+连接多个字符串，这个与其它数据库有点不一样，其他数据库用的是||

select 'A'+'b' as char

SUBSTRING函数：

截取字符串函数，语法为：substring(str，index，len)

就是截取从字符串的第index位置开始，截取len长度的字符串。

select substring('zainzhu',2,3) as char

replace函数

这个函数为替换函数，语法格式为：replace(str，str1，str2)

就是用str2去替换字符串str中str1，然后返回一个新的字符串。

select replace('zainzhu','zhu','it')

isnull函数：

这是关于空值的函数，语法格式为isnull(str1,str2)

如果字符串表达式str1为空，就返回字符串表达式str2。

否则就返回字符串表达式str1

注解：sqlserver中''不表示空，它不等同于null，这个和db2一样。

select isnull(null,'zain') as char

coalesce函数：

该返回不是null的第一个字符串，

语法格式为：COALESCE (expression_1, expression_2, ...,expression_n)

用法为函数列表中第一个非空的表达式是函数的返回值，如果所有的表达式都是空值，最终将返回一个空值

select coalesce(null,null,'zain')

convert函数：

数据类型的转换，利用这个函数我们可以进行任何数据的转变。

select convert(varchar,getdate(),20) as char

cast函数:
 select cast('12' as int)

 stuff函数;
   STUFF 函数将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置。可用于删除字符，截取字符，替换字符
select stuff('zainzhu',1,1,'') as char
select stuff('zainzhu',1,1,'aaaa') as char

for xml path函数:
select teacher,object  from teach  for xml path 

根节点默认是<row></row>,我们可以通过path后面的参数改变根节点名称：
select teacher,object  from teach  for xml path('记录') 

一个小技巧： 可以通过path('')来去掉根节点
select teacher,object  from teach  for xml path('') 

select teacher as '老师', 
(select  object+'，'  from  teach  t1 where  t1.teacher=t2.teacher   FOR xml path ('')) as '学科'
from  teach t2
group by  teacher