Friday, October 31, 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

  • Joes Quicklist Weblinks (JQW)

    • Mon 27-Oct-14
      Hi. I was able to download today with my laptop in a different location. Both computers using Ubuntu ...
      Webcrawler  
    • Sun 26-Oct-14
      Hmm... thanks Webcrawler! I can't replicate the error as all the links work for both my superuser ...
      Webmaster  
    • Sun 26-Oct-14
      Can't download new version. I get the same error message as Kat a few post's up.... CTRL+F5 does not ...
      Webcrawler  
    • Sat 25-Oct-14
      Hi Traveller, Apologies for the incredible delay as I have been focusing on another app in development.
      Webmaster
  • SSIS Script: convert UPPERCASE to Mixed-Case using TitleCase

    • Tue 28-Oct-14
      This post was immensely helpful, thank you. I used the code for the script component and made some ...
      Dave L .