Wednesday, October 22, 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

  • Convert to Proper Case in T-SQL

    • Tue 07-Oct-14
      Really comprehensive function. Nice work! Dave.
      starsky51
  • Joes Quicklist Weblinks (JQW)

    • Wed 08-Oct-14
      Thanks jazzmang! This module is due for an update so I'll review this issue in the next version.
      Webmaster  
    • Tue 07-Oct-14
      I've set "Display Images" under Module Settings but the modules continues to display a hard coded CSS ...
      jazzmang
  • Joes Word Cloud (JWC)

    • Thu 16-Oct-14
      hello i use your tag cloud component in my joomla 2.5 website. i'm french and there is probleme with ...
      plykite2010  
    • Tue 07-Oct-14
      Hello, i have Joomla 2.5.27 and Module JWC v3.2.2 installed. In Configuration i fill in how many Word ...
      Maic