sql：常用函数

1、验证是否是null，是则替换ISNULL(Weight, 50)

SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;

 

 

2、连接多个字符串 CONCAT(ename , ‘a’ , sal)

       SELECT  CONCAT(ename , ‘a’ , sal) from emp

 方法2：直接用 ‘+’

 

3、截取字符串SUBSTRING      (      expression      ,      start      ,      length      )   

 SELECT  SUBSTRING      (      expression      ,      start      ,      length      )    from emp
参数 
expression 
是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。 

start 
是一个整数，指定子串的开始位置。 

length 
是一个整数，指定子串的长度（要返回的字符数或字节数）。

substring() 
——任意位置取子串 

 

 

 

4、左端或者右端截取
left(  expression      ,      length      ) 
right(  expression      ,      length      ) 
——左右两端取子串 

ltrim() 左
rtrim() 右
——截断空格，没有trim()。 

 

 

 

5、查询字符串返回位置
CHARINDEX(expression1, expression2 [,start_location])

expression1,  子串

expression2  母串

[,start_location] 开始的位置

PATINDEX('%pattern%', expression) 

'%pattern%',    子串

expression  母串
——查子串在母串中的位置，没有返回0。区别：patindex支持通配符，charindex不支持。

 

 

6、case的两种形式

--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

 

 

（7-10选自http://www.jb51.net/article/34820.htm）

7、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count

(peopleId) > 1)

8、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people 
where peopleId in (select   peopleId from people group by   peopleId   having count

 

(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId)>1)

9、查找表中多余的重复记录（多个字段） 

select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having

count(*) > 1)

10、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having

count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)