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

← Expressions
ExcelOleDB@1 →