Saturday, October 25, 2014
   
Text Size
Login

Cheat Sheet for mySQL vs t-SQL

Some memory aids for me so that I don't have to keep looking them up:

MySQLT-SQL
Strings
String ReplaceREPLACE(haystack,needle,replacement)REPLACE(haystack,needle,replacement)
String PositionINSTR(haystack, needle)
LOCATE(needle, haystack [, offset])
PATINDEX(%needle%, haystack)
CHARINDEX(needle, haystack [, offset])
String ConcatenationCONCAT(string1,string2[,stringN])string1 + string2
SS v2012+:
CONCAT(string1, string2 [, stringN ])
String SubstringSUBSTRING( string,start,length )SUBSTRING( string,start,length )
String LengthLENGTH(string)
BIT_LENGTH(string)
CHAR_LENGTH(string)
LEN(string)
DATALENGTH(string)
Dates / Times
Weekday NameDAYNAME(now())DATENAME(dd, getdate())
Weekday NumberDAYOFWEEK(now())DATEPART(dw, getdate())
Month NameMONTHNAME(now())DATENAME(mm, getdate())
Month NumberMONTH(now())DATEPART(mm, getdate())
European Date (dd/mm/yyyy)DATE_FORMAT(now(), '%d/%m/%Y')CONVERT(varchar, getdate(), 103)
Time to SecondsTIME_TO_SEC(now())(DATEPART(hour, getdate()) * 3600) +
(DATEPART(minute, getdate()) * 60) +
(DATEPART(second, getdate()))
Seconds to TimeSEC_TO_TIME( seconds )CONVERT(varchar, DATEADD(ms, seconds * 1000, 0), 114)
Add/Subtract DateDATE_ADD(date,INTERVAL number datepart)
DATE_SUB(date,INTERVAL number datepart)
DATEADD(datepart, number, date)
DATESUB(datepart, number, date)
Other
If-then-elseIF( expression,value_if_true,value_if_false )IIF( expression,value_if_true,value_if_false )
If NullIFNULL(column_name,value_if_null)ISNULL(column_name,value_if_null)
Rank/Rownum (@rownum:=@rownum+1) AS Rownum
LIMIT from_index, number_of_rows
ROW_NUMBER() OVER(ORDER BY column_name ASC) AS Rownum


Source(s):
Add Comment

Name:

Email:

Website:

Message:


Latest Posts