Sunday, April 26, 2015
   
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:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 22-Apr-15
      Hi, It's there a way to capture the directions the Gmaps navigation gives you? Like, when you have ...
      Neno
  • Joes Quicklist Weblinks (JQW)

    • Thu 23-Apr-15
      I changed the sourcecode in the helper.php file at line 249 from $ch = ...
      Andy  
    • Thu 23-Apr-15
      Joomla 3.4.1 purity iii template
      Andy  
    • Thu 23-Apr-15
      Hi, I got on all my weblinks a black thumbnail with this error back: http%3A%2F%2Fwww.deutsche-wirtschafts-nachrichten.de ...
      Andy  
    • Thu 23-Apr-15
      Hi, I got on all my weblinks a black thumbnail with this error back: http%3A%2F%2Fwww.deutsche-wirtschafts-nachrichten ...
      Andy