Sometimes when you want to migrate from MS Accesss to SQL Server, you need to rewrite the queries compatible to SQL Server. As you know the basic systax is same but the functions used in Access should be replaced with their equivalent in SQL Server.
Here are some Access functions and their equivalent in SQL Server
---------------------------------------------------------------------------- ACCESS SQL Server ---------------------------------------------------------------------------- Instr Charindex Mid Substring Chr Char LCase Lower UCase UPPER StrConv Lower for value 1, UPPER for value 2, Initcap for value 3 & (concatenation) + Trim ltrim(rtrim()) Format Convert IIF CASE WHEN Switch CASE WHEN INT CAST(col as INT) or CONVERT(INT,col) Fix CAST(col as INT) or CONVERT(INT,col) Val Extract numbers
Initcap function http://www.beyondrelational.com/blogs/madhivanan/archive/2010/07/19/initcap-function.aspx Extract numbers http://www.beyondrelational.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx
Read Part II also
Tags: t-sql, sql_server, sqlserver, tsql, BRH, #TSQL, function, #SQLServer, Access,