List of supported functions

Bellow is a list of all the supported functions by csvsql

Binary operators

Operator	Description	Examples
+	Adds two numbers	4 + 5 will give us 9
*	Multiply two numbers	4 * 5 will give us 20
/	Divide two numbers	4 / 5 will give us 0.8, 20 / 2 will give us 2
-	Subtract two numbers	4 - 5 will give us -1
%	Find the Modulo of two numbers	7 % 5 will give us 2, 45 % 11 will give us 1
\|\|	Concat two strings	7 \|\| 5 will give us 75, 'one ' \|\| 'two' will give us one two
<	Return true only if the left argument is less than the right argument	7 < 5 will give us false, 5 < 7 will give use true
>	Return true only if the left argument is more than the right argument	7 > 5 will give us true
=	Return true if the left argument if the same as right	7 = 5 will give us false, 'one' = 'one' will give us true
<=	Return true only if the left argument is less or equals to the right argument	7 <= 5 will give us false
>=	Return true only if the left argument is more or equals to the right argument	7 >= 5 will give us true
!=	Return true if the left argument is not the same as the right argument (same as <>)	7 != 5 will give us true
<>	Return true if the left argument is not the same as the right argument (same as !=)	7 <> 5 will give us true
AND	Return true if both arguments are true, null if either arguments is not a Boolean, false in any other case	true AND true will give us true, true AND false will give us false
OR	Return false if both arguments are false, null if either arguments is not a Boolean, true in any other case	true OR true will give us true, false OR false will give us false
XOR	Return true if on argument is true and the other one is false, null if either arguments is not a Boolean, false in any other case	true XOR true will give us false, false XOR false will give us false
IN	Check is an expression value contains with in a list or a subquery	3 IN (4, 3, 1) or 5 IN (SELECT age FROM pets)
NOT IN	Negate the IN operator	3 NOT IN (4, 3, 1) or 5 NOT IN (SELECT age FROM pets)
BETWEEN	Check if an expression is between two numeric values (not the AND operator)	7 BETWEEN 5 AND 12
NOT BETWEEN	Negate the between operator	7 NOT BETWEEN 5 AND 12
RLIKE	Check if the expression matches a regular expression (Regular expression rules are defined in here)	'200' RLIKE '[0-9]+
NOT RLIKE	Negate the RLIKE operator	'200' NOT RLIKE '[0-9]+
SIMILAR TO	Same as RLIKE| ‘200’ SIMILAR TO ‘[0-9]+`
NOT SIMILAR TO	Same as NOT RLIKE| ‘200’ NOT SIMILAR TO ‘[0-9]+`
REGEXP	Same as RLIKE| ‘200’ REGEXP ‘[0-9]+`
NOT REGEXP	Same as NOT RLIKE| ‘200’ NOT REGEXP ‘[0-9]+`

Unary operators

Please note, some operator has prefix format and some postfix format.

Operator	Description	Examples
IS FALSE	Check if the expression is false (will return true if it’s false or false for any other value)	1 + 1 IS FALSE
IS NOT FALSE	Check if the expression is not false (will return false if it’s false or true for any other value)	1 + 1 IS NOT FALSE
IS TRUE	Check if the expression is true (will return true if it’s true or false for any other value)	1 + 1 IS TRUE
IS NOT TRUE	Check if the expression is not true (will return false if it’s true or true for any other value)	1 + 1 IS NOT TRUE
IS NULL	Check if the expression is empty	1 + 1 IS NULL
IS NOT NULL	Check if the expression is not empty	1 + 1 IS NOT NULL
NOT	return true if the expression is false or false if the expression is true, null for non Boolean expression. Note, this is a prefix operator	NOT 1 > 3
-	If the expression is numeric, return the negative value of that expression. Note, this is a prefix operator	- (3 + 1)
+	If the expression is numeric, return the value of that expression. Note, this is a prefix operator	+ (3 + 1)

Aggregation Functions

Function	Description	Examples
COUNT	Counts the number of items. One can use COUNT(*) or COUNT(DISTINCT age)	COUNT(id)
MAX	Returns the maximal value	MAX(age)
MIN	Returns the minimal value	MIN(age)
AVG	Returns the average value	AVG(price)
SUM	Returns the sum of all the values	SUM(price)
ANY_VALUE	Return any value from the group	ANY_VALUE(date)

Functions

Function	Description	Examples
TRY_CAST	Will cast an expression to another datatype, if failed, will return null	TRY_CAST('1002' AS INT)
CAST	same as TRY_CAST	CAST('1002' AS INT)
EXTRACT	extract value from a date or a timestamp field	EXTRACT(day FROM '2025-03-10') or EXTRACT(hour FROM '2025-03-10 20:00:10')
CEIL	Return the ceiling of a number	CEIL(10.32)
FLOOR	Return the floor of a number	FLOOR(10.32)
POSITION	Return the one based index of a substring within a string (will return null if either argument is not a string)	POSITION('old' IN 'gold')
SUBSTRING	Create a substring from a string. Can have two (the string and the start index - one based) or three (the maximal length of the results) arguments	SUBSTRING('Gold' FROM 2) or SUBSTRING('gold' FROM 2 FOR 1)
ABS	Return the absolute value of a number	ABS(22)
ASCII	Returns the ascii value of the first character of a string argument	ASCII('a')
CHR	Return the character of an ascii value of a numeric argument	CHR(97)
LENGTH	Return the number of character in a string argument	LENGTH('Test')
CHAR_LENGTH	Same as length	CHAR_LENGTH('Test')
CHARACTER_LENGTH	Same as length	CHARACTER_LENGTH('Test')
COALESCE	Same the first non empty argument	CHARACTER_LENGTH(NULL, NULL, 4)
CONCAT	Concatenate all the arguments into a string	CONCAT('h', 'e', 'll', 'o', ' ', 'world')
CONCAT_WS	Concatenate all the arguments from the second one to a string with the first argument as a separator	CONCAT_WS(' ', 'hello', 'world')
CURRENT_DATE	Return the current date (in UTC)	CURRENT_DATE()
CURDATE	Same as CURRENT_DATE	CURDATE()
NOW	Return the current timestamp in UTC	NOW()
CURRENT_TIME	Same as NOW	CURRENT_TIME()
CURRENT_TIMESTAMP	Same as NOW	CURRENT_TIMESTAMP()
CURTIME	Same as NOW	CURTIME()
LOCALTIME	Same as NOW (notice, this is not local)	LOCALTIME()
CURRENT_TIME	Same as NOW (notice, this is not local)	CURRENT_TIME()
USER	Return the os username	USER()
CURRENT_USER	Same as USER	CURRENT_USER()
FORMAT	Format date or timestamp to a string. See available formats in chron docs	FORMAT(NOW(), '%c')
DATE_FORMAT	Same as FORMAT	DATE_FORMAT(NOW(), '%c')
TIME_FORMAT	Same as FORMAT	TIME_FORMAT(NOW(), '%c')
TO_CHAR	Same as FORMAT	TO_CHAR(NOW(), '%c')
TO_TIMESTAMP	Create a timestamp from the number of seconds since Unix epoch	TO_TIMESTAMP(1400234500)
FROM_UNIXTIME	Same as TO_TIMESTAMP	FROM_UNIXTIME(1400234500)
GREATEST	Return the greatest of all the arguments	GREATEST(100, 20, 102, 80)
LEAST	Return the lower of all the arguments	LEAST(100, 20, 102, 80)
IF	If the first argument is true return the second argument, if it is false returns the second argument	IF(5 > 10, 'Yes', 'No')
NULLIF	If the first argument is the same as the second argument, return empty value, if they are not the same, return the first argument	NULLIF(1, 10)
LOWER	Convert a string to lower case	LOWER('HELLO')
LCASE	Same as LOWER	LCASE('HELLO')
UPPER	Convert a string to upper case	UPPER('hello')
UCASE	Same as UPPER	UCASE('hello')
LEFT	Take the first n character of a string	LEFT('hello world', 5)
RIGHT	Take the last n character of a string	RIGHT('hello world', 5)
LPAD	pad text from the beginning so it will be in a given length	LPAD(' ', 'test', 8)
RPAD	pad text from the end so it will be in a given length	RPAD(' ', 'test', 8)
LTRIM	remove any leading white space characters	LTRIM(' hello')
RTRIM	remove any trailing white space characters	RTRIM('hello ')
PI	Return PI (up to 10 digits)	PI()
RANDOM	If it has no argument, return a random number between 0 and 1. If it has a positive numeric argument, return a random integer number between the 0 and the number. Note, this will not use a secure random generator.	RANDOM() or RANDOM(10)
RAND	Same as RANDOM.	RAND() or RAND(10)
POSITION	Returns the position of a substring within a string (1 based index). If it has a third numeric argument, will start the lookup from that index	POSITION('str', 'full string') or POSITION('str', 'full string', 2)
LOCATE	Same as POSITION	LOCATE('str', 'full string') or LOCATE('str', 'full string', 2)
REPEAT	Repeat a string argument a few times	REPEAT('Test', 2)
REPLACE	Replace all the occurrences of a string within a string with another string	REPLACE('text', 't', '-')
REGEX_LIKE	With two argument behave like the RLIKE operator. Adding a third argument will add flags to the regular expression. See available flags in the regex doc	REGEX_LIKE('200', '[0-9]+') or REGEX_LIKE('Hello', '[a-z]+', 'i')
REGEX_REPLACE	Replace all the occurrences of a regular expression with a given string	REGEX_REPLACE('10 + 10 = 20', '[0-9]+', '<number>')
REGEXP_SUBSTR	Finds a substring that match a regular expression. The third optional argument can be the position to start looking from (1 based index), The fourth optional index can be thee occurrence number to find (default to the first occurrence, 1 based index), the fifth optional argument can be the regular expression flags	REGEXP_SUBSTR('this 100 is a number', '[0-9]+') or REGEXP_SUBSTR('this 100 is a number', '[a-z]+', 5) or REGEXP_SUBSTR('this 100 is a number', '[a-z]+', 5, 2) or or REGEXP_SUBSTR('this 100 is a number', '[a-z]+', 5, 2, 'i')
REVERSE	Reverse a string argument	REVERSE('some')
LN	Finds the natural logarithm of a number	LN(100)
EXP	Finds the natural exponent of a number	EXP(100)
LOG	With a single numeric argument, finds the 10 based logarithm of the number. With two arguments, find the first argument logarithm of the second argument	LOG(100) or LOG(3, 9)
LOG2	Finds the 2 based logarithm of the number	LOG2(16)
LOG10	Finds the 10 based logarithm of the number	LOG10(1000)
POW	Find the first argument to the power of the second argument	POW(2, 4)
POWER	Same as POW	POWER(2, 4)
ROUND	With a single argument, round the value of the argument to the nearest integer. With two arguments, round the value of the first argument to the second argument digits after the decimal point.	ROUND(1.35) or ROUND(1.411, 2)
SQRT	Finds the square root of a number.	SQRT(64)

Case function

The case function has a few conditions, and the return value will be the first condition that is true. If no condition is true, we will use the ELSE value, if there is no else value, we will default to null. For example:

       CASE
         WHEN "delivery cost" < 0.5 THEN 1
         WHEN "delivery cost" < 1 THEN 2
         WHEN "delivery cost" < 10 THEN 3
         ELSE 4
       END AS "one",

or

       CASE
         WHEN "delivery cost" < 0.5 THEN "delivery cost"
         WHEN "delivery cost" < 1 THEN "delivery cost" / 2
         WHEN "delivery cost" < 10 THEN "delivery cost" / 10
       END AS "two",