SQL Server

1)字母大小写敏感的写法

select *
from sys.fn_helpcollations()
where name like 'Latin1_General_CS_AS'

//大小写敏感

select *
from [User]
where LastName COLLATE Latin1_General_CS_AS like 'Admin'

2)SQL 中[]和“” 的一些功能

select 'dd' as "first name"

select 'dd' as [first name]

3)+注意　　

select 'dd'+null ==> null

SET CONCAT_NULL_YIELDS_NULL OFF;//add this to change regle

select 'dd'+ null ==> dd

4)data convert 时间转化

select  CONVERT(datetime,convert(nvarchar,GETDATE(),110),110) between FromDate and ToDate

select CONVERT(date,getdate(),110) //更好的解决方案

SELECT  CAST(SYSDATETIME() AS DATE) AS [current_date] //2008 sql server

5)字符串处理

SELECT SUBSTRING('abcde', 1, 3); ==>abc

LEFT( string, n ), RIGHT( string, n )

LEN( string ); 字符个数

SELECT LEN(N'abcde');所占字符数

SELECT DATALENGTH(N'abcde');所占字节数

SELECT CHARINDEX(' ','Itzik Ben-Gan');

SELECT PATINDEX('%[0-9]%', 'abcd123efgh');

SELECT REPLACE('1-a 2-b', '-', ':');

SELECT REPLICATE('abc', 3);

select right(REPLICATE('0', 9)+CAST(Id AS VARCHAR(10)),10)

STUFF( string, pos, delete_length, insertstring )

SELECT STUFF('xyz', 2, 1, 'abc');

UPPER( string ), LOWER( string )

SELECT RTRIM(LTRIM(' abc '));

% :many

_  :single

select * from [User] where FirstName like N'[ABC]%'

select * from [User] where FirstName like N'[A-E]%'

select * from [User] where FirstName like N'[^A-E]%'