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)

Latest news