Database specific functions that may be needed
Function | Database | Use |
Table name | Oracle | Table_name |
Access | Table_name | |
Excel | [sheet_name$] | |
My SQL | Table_name | |
Column name | Oracle | Table_name.Column_name |
Access | Table_name.Column_name | |
Excel | [sheet_name$].Column_tittle | |
My SQL | Table_name.Column_name | |
Column synonym | Oracle | Column_name synonym |
Access | Column_name as synonym | |
Excel | Column_tittle as synonym | |
My SQL | Column_name synonym | |
Convert a number to character string | Oracle | to_char(number) |
Access | format(number) | |
Excel | format(number) | |
My SQL | cast(number as char) | |
Pad ‘0’ on the left side | Oracle | lpad(field_name,length,’0’) |
Access | format(field_name,’0000’) (for 4 characters) | |
Excel | format(field_name,’0000’) (for 4 characters) | |
My SQL | lpad(field_name,length,char) | |
Convert a date to a character string at ISO format | Oracle | to_char(field_date,’YYYY-MM-DDTHH24:MI’) |
Access | format(field_date,'yyyy-mm-ddThh:mm') | |
Excel | format(field_date,'yyyy-mm-ddThh:mm') | |
My SQL | concat(year(field_date),'-',lpad(month(field_date),2,'0'), '-', lpad(day(field_date),2,'0'),'T',lpad(hour(field_date),2,'0'),':', lpad(minute(field_date ),2,'0')) | |
Give the system date | Oracle | sysdate |
Access | date() | |
Excel | date() | |
My SQL | now() | |
If Then Else | Oracle | decode(field_name,’val1’,’val2’,’other’) |
Access | iif(field_name=’val1’,’val2’,’other’) | |
Excel | iif(field_name=’val1’,’val2’,’other’) | |
My SQL | (case field_name when ‘val1’ then ‘val2’ else ‘other’ end) |
