Functions

Do you need to compute something from numbers, dates, texts you got? No problem.

Text Functions #

So were tasked to check, whether all Last Name column values in the MS Excel sheet start with a capital letter, right? Text functions are a necessity for such situation.

SELECT
  concat('Testing ', 'with ', 'CAT ', 'is ', 'cool ') as ConcatStrings,
  contains('Testing with CAT is cool.', 'cool') as SearchForString,
  format ('Testing with {} is {}', 'CAT', 'cool') as FormatStrings,
  lower('CAT') as LowerCase, -- returns cat
  upper('cat') as UpperCase, -- returns CAT
  left('CAT', 2) as LeftPartOfString, -- returns CA
  right('CAT', 2) as RightPartOfString, -- returns AT
  length('CAT') as LengthOfString, -- returns 3
  position('Testing with CAT is cool', 'with') as FirstPositionOfString, -- returns 9, if not found 0
  trim(' CAT is cool  ') as TrimmedString

The example extracts only the most common functions. There are also many regular expression functions and lots of others text functions.

Details: https://duckdb.org/docs/archive/0.8.1/sql/functions/char

Date and Time Functions #

MS Excel data may contain date or time or datetime values. Some useful functions:

SELECT  current_date,
        datediff('hour', DEPARTURE_TIME, LANDING_TIME),
        datepart('year', DAY_OF_FLIGHT),
        dayname(DEPARTURE_DATE), -- Sunday, Monday, ...
        least(DEPARTURE_TIME, LANDING_TIME), -- lower of the two dates
        greatest(DEPARTURE_TIME, LANDING_TIME), -- bigger of the two dates
        make_date(2000, 1, 1), -- creates a date from parts,
        monthname(DEPARTURE_DATE) -- January, February, ...
FROM    FACT.DEPARTURES

Complete reference: https://duckdb.org/docs/archive/0.8.1/sql/functions/date.

Hopefully the examples might get you going without digging too much into details. If not, feel free to find in DuckDB documentation details for whatever function you need:

https://duckdb.org/docs/archive/0.8.1/sql/functions/overview