下面记录一些用到sql server查询时候用到的函数。
charindex,在一个表达式中搜索另一个表达式并返回其起始位置（如果找到）。http://msdn.microsoft.com/zh-cn/library/ms186323.aspx
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
执行区分大小写的搜索
USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST','Das ist ein Test'COLLATE Latin1_General_CS_AS);
执行不区分大小写的搜索
USE tempdb;
GO
SELECT CHARINDEX ( 'TEST','Das ist ein Test'COLLATE Latin1_General_CI_AS)
GO
order by,对查询所返回的数据进行排序。http://msdn.microsoft.com/zh-cn/library/ms188385
ORDER BY order_by_expression[ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]
[ <offset_fetch> ]<offset_fetch> ::=
{ OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }[FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]
}
将表达式指定为排序列
USE AdventureWorks2012;
Go
SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY DATEPART(year, HireDate);指定条件顺序
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryNameELSE CountryRegionName END;自己使用了一个需要排序某个值靠前。
select pagename, CASE pagename WHEN 'home' THEN -1 else 1 end orderNum from friendlink group by pagename order by orderNum,pagename asc
