Nik's Technology Blog

Travels through programming, networks, and computers

T-SQL to MySQL Conversions

I've been converting my live web statistics page to PHP and MySQL and after spending a while trying to find MySQL alternatives to Transact SQL (T-SQL) only SQL statements, I thought I'd share a few common solutions to the problems I eventually managed to solve.

Microsoft developers commonly use TOP in a select statement to select the first n rows from a table. The MySQL equivalent is LIMIT, which is superior in that you can specify what row to start on and well as how many to retrieve.

SELECT TOP 20 * FROM MY_TABLE

SELECT * FROM MY_TABLE LIMIT 0,20

Other problems arise from T_SQL's use of Visual Basic (VB). So statements using LCASE, UCASE, INSTR and MID won't work. MySQL of course has alternatives...

SELECT LOWER(name) FROM MY_TABLE

SELECT UPPER(name) FROM MY_TABLE

The SUBSTRING MySQL statement is equivalent to MID or LEFT if you start at the first character. The following statement would result in abcd.

SELECT SUBSTRING('abcdefghijklmnopqrstuvwxyz',1,4)

VB's INSTR is another useful function in T-SQL, the MySQL equivalent is LOCATE. Like INSTR you define a starting point in which you would like to start searching within the string. The following SQL statement would result in 4.

SELECT LOCATE('d','abcdefghijklmnopqrstuvwxyz',1)

MySQL's version of T-SQL's LEN function works in exactly the same manor, only it's called LENGTH. The following statement will result in 26.

SELECT LENGTH('abcdefghijklmnopqrstuvwxyz')

MySQL Manual - String Functions

Comments are closed