test page Ocient

89 min

The supports querying using the SQL syntax that follows ANSI SQL standards.

Ocient SQL Syntax

@virtual

This code block shows the general syntax to perform SQL querying in and the order in which commands should go.

For specific descriptions and syntax for the commands, see the respective SQL statement sections on this page.

Syntax

[ WITH ... ]
SELECT ...
    [ EXCEPT(...) ]
[ FROM ...
    [ JOIN ... ] ]   
[ WHERE ... ]
[ GROUP BY ... 
    [ HAVING ... ] ]
[ ORDER BY ... ] 
[ LIMIT ... ]
[ OFFSET ... ] 
[ INTERSECT ... ]
[ EXCEPT ... ]
[ UNION ... ] ]         
[ USING ... ]
[ TRACE ... ] 
[ TAG ... ]

Default Schema

Ocient identifies every table using a database and schema. For example, the fully qualified path to the movies table is cinema.adventure.movies, where cinema is the database and adventure is the schema.

When you do not fully qualify a table name, the Ocient System uses a default schema. When you first log into the system, the default schema is your fully qualified username. You can change the default schema using the SET SCHEMASET SCHEMA command.

Querying SQL Statement Reference

Ocient supports the following SQL statements.

WITH

Assigns a name to a common table expression, allowing an auxiliary query to be used in the main query. This is helpful for breaking down complex queries into smaller parts.

Syntax

WITH 
    <cte_name> [ ( <cte_column> [ ,... ] ) ] 
    AS ( <sub_query> )
<select_query>

Parameters

Parameter	Description
<cte_name>	A name for the common table expression populated with the result set from the sub_query statement.
<cte_column>	Optional. This is a list of column names for common table expressions populated with the sub_query result set. If no column names are provided, the column names are the same as the table referenced in the sub_query statement.
<sub_query>	An auxiliary query that collects data for a common table expression. sub_query can use regular query commands, including ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, WHERE, GROUP BY, and HAVING. See the respective SQL statement reference sections for usage rules.
<select_query>	The main SELECT query. Note that the FROM statement in the main SELECT query must also include the cte_name if the query uses its values.

Example

In this example, the subquery calculates the average budget for all rows in the movies table. The main query uses that average to find all movies that spent more.

WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget;

Output

title	budget	revenue
CGI Why	237000000	2787965087
Titania	200000000	1845034188
Merchandise Vehicle 5	200000000	1066969703
The Tentpole	220000000	1519557910
Pirates of Palm Springs	140000000	655011224
Spyman 16	200000000	1108561013
Frigid	150000000	1274219009
Fury 7	190000000	1506249360
Superhero 23	250000000	1084939099
Triassic World	150000000	1513528810
Iron Chef 3	200000000	1215439994
The Last Airman	150000000	318502923

SELECT

Initiates a query statement or a subquery clause within other statements.

You can query the data of tables where you have the SELECT privilege.

For information on using SELECT as a subquery for filtering or ordering results, see the WHEREWHERE and HAVINGHAVING sections.

For information on using SELECT as a subquery for a common table expression, see the WITHWITH section.

Syntax

SELECT [ ALL | DISTINCT ] 
    [ * [ EXCEPT ( column_name [ , ... ] ) ] 
    | <select_list_entry> [ , ... ] ]
    [ <from_clause> ]

Parameters

Parameter	Description
ALL	SELECT and SELECT ALL are both the same. Returns all valid data rows from the database that meet the criteria of your query.
DISTINCT	SELECT DISTINCT returns only unique rows that do not match other rows based on the criteria of your query.
*	Returns all columns from the specified tables in the result set for the query.
EXCEPT	When used with *, EXCEPT allows specific columns to be excluded from the query results.
column_name	The name of one or more columns from the specified table that you want to exclude (using EXCEPT) from your query results.

<select_list_entry>

The select_list_entry defines a column or expression to include in your query result set.

Syntax

<select_list_entry> ::= 
    column_name | expression [ AS new_name ]

Parameters

Parameter	Description
column_name	The name of one or more columns from the specified table that you want to include in your query results.
expression	One or more expressions that you want to include in your query results. Expressions can be any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.
new_name	An alias, treated as an identifier, for an alternative name for the column or the results of the expression.

<from_clause>

For information on the <from_clause>, see the FROMFROM documentation.

Examples

Using SELECT *

This example uses SELECT * to return all the columns in the movies table.

SELECT * FROM movies LIMIT 5;

Output

movie_id	title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
211672	Minnows	74000000	875.581305	2015-06-17	1156730962	91	Released	6.40	4571
24	Billy the Killy	30000000	79.754966	2003-10-10	180949000	111	Released	7.70	4949
19995	CGI Why	237000000	150.437577	2009-12-10	2787965087	162	Released	7.20	11800
37724	Spyman 16	200000000	93.004993	2012-10-25	1108561013	143	Released	6.90	7604
24428	The Tentpole	220000000	144.448633	2012-04-25	1519557910	143	Released	7.40	11776

Using SELECT * EXCEPT

This example uses SELECT * EXCEPT to exclude certain columns from the result set.

SELECT *
EXCEPT (movie_id, runtime, vote_average, vote_count)
FROM movies
LIMIT 5;

Output

title	budget	release_date	revenue
Swords & Scabbards	94000000	2003-12-01	1118888979
Billy the Killy	30000000	2003-10-10	180949000
Merchandise Vehicle 5	200000000	2010-06-16	1066969703
CGI Why	237000000	2009-12-10	2787965087
Space Odyssey 6000	10500000	1968-04-10	68700000

Using Lateral Column Aliases

Ocient SQL queries support lateral column aliases, meaning you can immediately reuse aliases for calculations in the same query as new inputs. Hence, you can simplify queries that normally require subqueries and common table expressions.

These examples use the products table with these columns:

product_id — Product identifier as an integer

product_name — Product name as a string

price — Price as a floating point number

Create this table using the CREATE TABLE SQL statement.

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
    );

Insert four records into the products table.

INSERT INTO products (product_id, product_name, price) VALUES
    (1, 'Laptop', 1000.00),
    (2, 'Tablet', 500.00),
    (3, 'Smartphone', 800.00),
    (4, 'Monitor', 300.00);

Create a query to determine prices after discounts and taxes by using a common table expression subquery DiscountedPrices. Use the WITH keyword to create the subquery.

WITH DiscountedPrices AS (
    SELECT 
        product_id,
        product_name,
        price,
        price * 0.90 AS discounted_price,
        price * 0.90 * 1.05 AS total_price_after_tax
    FROM 
        products
)
SELECT 
    product_id,
    product_name,
    price,
    discounted_price,
    total_price_after_tax
FROM 
    DiscountedPrices;

Lateral aliases allow the same calculations to be packaged in a single query.

This simpler query is essentially the same as the longer common table expression example, but the logic is condensed because you can reference the discounted_price alias immediately to calculate the total_price_after_tax value in the same query.

SELECT 
    product_id,
    product_name,
    price,
    price * 0.90 AS discounted_price,
    discounted_price * 1.05 AS total_price_after_tax
FROM 
    products;

FROM

Specifies the table or view to use in a SELECT statement.

Syntax

<select_clause> FROM 
    { table_name | ( <sub_query> ) } [ ,... ]
    [ <join_clause> [ ,... ] ]

Parameters

Parameter	Description
<select_clause>	A query statement using the SELECT SQL statement. For details, see the SELECTSELECT section.
table_name	The names of one or more tables or views that you want to query. If you specify multiple tables in the FROM list, separated by commas, the effect is the same as if you explicitly perform a CROSS JOINCROSS JOIN on all of those sources.
<sub_query>	One or more subqueries using the SELECT SQL statement. A subquery generates a table from which the select_clause query references data. Each subquery must be enclosed in parentheses with an optional correlation name. For details, see the SELECTSELECT section.
<join_clause>	A JOIN clause used to retrieve data from two or more tables for your query. For details, see the JOINJOIN section.

JOIN

Combines rows from multiple tables so they can be accessed by a query.

Syntax

<select_query> 
    FROM <table_reference1> <join_operation> <table_reference2> 
        ON table1_column <boolean_operator> table2_column

Parameter

Parameter	Description
<select_query>	A valid SELECT query. For details, see SELECTSELECT.
<table_reference1>	Either a table name or a full SELECT in parentheses table_reference1 takes precedence for returning rows for LEFT OUTER JOIN, SEMI JOIN, and ANTI JOIN. For specific rules, see Types of Join OperationsTypes of Join Operations.
<table_reference2>	Either a table name or a full SELECT in parentheses function. table_reference2 takes precedence for returning rows for RIGHT OUTER JOIN. For specific rules, see Types of Join OperationsTypes of Join Operations.
table1_column	A column from either table_reference The JOIN operation uses this column to match rows with table2_column to combine data for the result set.
<boolean_operator>	Join conditions can use any Boolean expression, including =, !=, <, >, =>, and <=
table2_column	A column from the table_reference not used for table1_column The JOIN operation uses this column to match rows based on the table1_column to combine data for the result set.

Types of Join Operations ( <join_operation> )

Ocient supports the following types of JOIN operations.

Syntax

<join_operation> ::=   
    { INNER JOIN
    | LEFT [ OUTER ] JOIN
    | RIGHT [ OUTER ] JOIN
    | FULL [ OUTER ] JOIN
    | CROSS JOIN
    | SEMI JOIN 
    | ANTI JOIN }

JOIN Type Descriptions

JOIN Type	Description
INNER JOIN	Returns all rows that exist in both tables. This join type is the default type of join.
LEFT OUTER JOIN	Returns all rows from the first table regardless of whether there are matching rows in the second table. LEFT JOIN performs the same operation as LEFT OUTER JOIN.
RIGHT OUTER JOIN	Returns all rows from the second table regardless of whether there are matching rows in the first table. RIGHT JOIN performs the same operation as RIGHT OUTER JOIN.
FULL OUTER JOIN	Returns all matched and unmatched rows. FULL JOIN performs the same operation as FULL OUTER JOIN.
CROSS JOIN	Returns the Cartesian product of the joined tables. Each row in the first table is joined with all rows in the second table. The result set contains the number of rows in the first table multiplied by the number of rows in the second table.
SEMI JOIN	Returns the rows from the first table that have at least one match in the second table. No columns from the second table are available in the query.
ANTI JOIN	Returns the rows from the first table that have no matches in the second table. No columns from the second table are available in the query.

By default, JOIN statements that involve subqueries can operate laterally if necessary. This behavior allows subqueries to reference joined columns from the preceding items included in the FROM clause.

For example, both parts of this join operation use subqueries that reference table x. The LATERAL keyword is optional; the join operation acts the same regardless of whether it is included.

SELECT * FROM ( SELECT 1 AS cx1, 2 AS cx2) AS x INNER JOIN LATERAL ( SELECT x.cx1 AS cy1, x.cx2 AS cy2) AS y ON x.cx1 = y.cy1;

Lateral joins are primarily useful when a cross-referenced column is necessary for computing the rows to join. A common application is providing an argument value for a set-returning function.

Examples

These examples join two tables:

games — A table of video game titles and their genre_ids. Note that some games have a NULL value assigned to their genre_id.

game_name	genre_id
Star Battle 6000	9
Dwarf Simulator	NULL
2002 Futbol	11
Space Attack!	9
Fantasy Fool	8
Blasto the Squirrel	5
Trucker Quest	7
Block, Stack, and Cry!	6
Basketball Day	11
Pizza Mutant	1
Italian Plumber	5
Zombies!	1
Space Ninjas	12
Moon Mayor	10
Spreadsheet Hero	NULL
Zoom Tycoon	12
Porch Poacher	6

genre — A table of video game genres, which are identified by IDs.

genre_name	id
Racing	7
Puzzle	6
Adventure	2
Simulation	10
Shooter	9
Misc	4
Action	1
Sports	11
Role-Playing	8
Platform	5
Strategy	12
Fighting	3

INNER JOIN

This example uses an INNER JOIN operation to capture only the rows that exist in both tables. Games with NULL values for their genre_id are eliminated from the result set.

SELECT game_name,
    genre_name
FROM video_games.game
    INNER JOIN video_games.genre ON game.genre_id = genre.id;

Output

game_name	genre_name
Fantasy Fool	Role-Playing
Blasto the Squirrel	Platform
Italian Plumber	Platform
Porch Poacher	Puzzle
Space Ninjas	Strategy
Basketball Day	Sports
2002 Futbol	Sports
Moon Mayor	Simulation
Block, Stack, and Cry!	Puzzle
Zombies!	Action
Pizza Mutant	Action
Space Attack!	Shooter
Trucker Quest	Racing
Zoom Tycoon	Strategy

LEFT OUTER JOIN

This example uses a LEFT OUTER JOIN operation to capture all rows from the left table (game), even if they have no matching row in the right table (genre).

SELECT game_name,
    genre_name
FROM video_games.game
    LEFT OUTER JOIN video_games.genre ON game.genre_id = genre.id;

Output

game_name	genre_name
Fantasy Fool	Role-Playing
Space Ninjas	Strategy
Zoom Tycoon	Strategy
Zombies!	Action
Pizza Mutant	Action
Trucker Quest	Racing
Blasto the Squirrel	Platform
2002 Futbol	Sports
Basketball Day	Sports
Italian Plumber	Platform
Block, Stack, and Cry!	Puzzle
Star Battle 6000	Shooter
Dwarf Simulator	NULL
Moon Mayor	Simulation
Porch Poacher	Puzzle
Spreadsheet Hero	NULL
Space Attack!	Shooter

RIGHT OUTER JOIN

This example uses a RIGHT`` OUTER JOIN operation to capture all rows from the right table (genre), even if they have no matching row in the left table (game).

SELECT game_name,
    genre_name
FROM video_games.game
    RIGHT OUTER JOIN video_games.genre ON game.genre_id = genre.id;

Output

game_name	genre_name
NULL	Adventure
Trucker Quest	Racing
Pizza Mutant	Action
Moon Mayor	Simulation
Fantasy Fool	Role-Playing
Space Attack!	Shooter
NULL	Misc
Star Battle 6000	Shooter
Space Ninjas	Strategy
Porch Poacher	Puzzle
Block, Stack, and Cry!	Puzzle
Blasto the Squirrel	Platform
Italian Plumber	Platform
Zoom Tycoon	Strategy
NULL	Fighting
Zombies!	Action
2002 Futbol	Sports
Basketball Day	Sports

FULL OUTER JOIN

This example uses a FULL OUTER JOIN operation to capture all rows from both tables, even if they do not match.

SELECT game_name,
    genre_name
FROM video_games.game
    FULL OUTER JOIN video_games.genre ON game.genre_id = genre.id;

Output

game_name	genre_name
Basketball Day	Sports
2002 Futbol	Sports
Zombies!	Action
Blasto the Squirrel	Platform
Moon Mayor	Simulation
Trucker Quest	Racing
NULL	Misc
Star Battle 6000	Shooter
Space Attack!	Shooter
Spreadsheet Hero	NULL
NULL	Adventure
NULL	Fighting
Italian Plumber	Platform
Pizza Mutant	Action
Dwarf Simulator	NULL
Fantasy Fool	Role-Playing
Block, Stack, and Cry!	Puzzle
Space Ninjas	Strategy
Zoom Tycoon	Strategy
Porch Poacher	Puzzle

CROSS JOIN

This example uses a CROSS JOIN operation to capture every possible combination of rows from both tables, regardless of whether they match. Note that unlike other JOIN operations, CROSS JOIN does not require an ON statement.

SELECT game_name, 
    genre_name 
FROM video_games.genre 
    CROSS JOIN video_games.game;

Output

As the result set for this CROSS JOIN example is more than 200 rows, the results are abbreviated.

genre_name	genre_id
Spreadsheet Hero	Role-Playing
Spreadsheet Hero	Misc
Spreadsheet Hero	Sports
Spreadsheet Hero	Platform
Spreadsheet Hero	Strategy
Spreadsheet Hero	Shooter
Spreadsheet Hero	Fighting
Spreadsheet Hero	Action
Spreadsheet Hero	Simulation
Spreadsheet Hero	Racing
Spreadsheet Hero	Puzzle
Spreadsheet Hero	Adventure
Space Attack!	Role-Playing
Space Attack!	Misc
Space Attack!	Sports
Space Attack!	Platform
Space Attack!	Strategy
Space Attack!	Shooter
Space Attack!	Fighting
Space Attack!	Action
Space Attack!	Simulation
Space Attack!	Racing
Space Attack!	Puzzle
Space Attack!	Adventure
...	...

SEMI JOIN

This example uses a SEMI JOIN operation to capture genre names that have at least one match in the games table. Rows are only included once, even if there are multiple matches.

SELECT genre_name, 
    id 
FROM video_games.genre 
    SEMI JOIN video_games.game ON genre.id = game.genre_id;

Output

genre_name	genre_id
Simulation	10
Shooter	9
Racing	7
Strategy	12
Puzzle	6
Platform	5
Action	1
Sports	11
Role-Playing	8

ANTI JOIN

This example uses an ANTI JOIN operation to capture any rows in the game table that do not match any rows in the genre table.

SELECT game_name
FROM video_games.game 
    ANTI JOIN video_games.genre ON game.genre_id = genre.id;

Output

game_name
Spreadsheet Hero
Dwarf Simulator

WHERE

Filters rows based on a specified condition.

Syntax

<select_query> 
    WHERE { column_name <filter_condition> filter_value | 
    [ NOT ] EXISTS ( <sub_query> ) }

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
column_name	A column used for grouping that is evaluated by the filter_condition.
filter_value	A value used to evaluate the specified column_name. For details, see the <filter_condition><filter_condition> section.
<sub_query>	A subquery with a filter condition that follows the EXISTS clause. For details, see the EXISTSEXISTS section.

<filter_condition>

A logical combination of predicates used to evaluate the referenced column_name based on the filter_value.

Syntax

<filter_condition> ::= 
  { =
    | ==
    | <>
    | !=
    | [ NOT ] EQUALS
    | <
    | <=
    | >
    | >=
    | [ NOT ] IN
    | [ NOT ] LIKE
    | [ NOT ] SIMILAR TO
    | BETWEEN
    | FOR SOME
    | FOR ALL
    | IS [ NOT ] NULL
    | IS [ NOT ] DISTINCT FROM 
  }

Definitions

Operator	Description	Example
=, ==, EQUALS	Equal to.	Author = 'Alcott'
<>, !=	Not equal to	Dept <> 'Sales'
>	Greater than	Hire_Date > '2012-01-31'
<	Less than	Bonus < 50000.00
>=	Greater than or equal	Dependents >= 2
<=	Less than or equal	Rate <= 0.05
[ NOT ] LIKE	Begins with a character pattern to match. The pattern is enclosed in parentheses and can include the wildcard characters %, representing zero, one or multiple characters, and _ representing a single character. To have a literal % or _ that does not act as a wildcard on the right-hand side of a LIKE or NOT LIKE expression, put a backslash before the character.	Full_Name LIKE 'Will%'
[ NOT ] SIMILAR TO	For usage information, see the SIMILAR TO OperatorSIMILAR TO Operator section.	'abc' SIMILAR TO 'abc'
BETWEEN value1 AND value2	Requires two values. The filter evaluates to TRUE if the specified column is within the range between the two values. BETWEEN bounds are inclusive. Values can be times, numbers, or strings.	Sales BETWEEN 1000 AND 5000
FOR_SOME()	Evaluates an array to determine if at least one value meets the filter criteria. In the example, the FOR_SOME() operator would return all rows of the column col_int_array where at least one value is greater than 10. For details, see Array FiltersArray Filters.	FOR_SOME(col_int_array) > 10 FOR_SOME(col_int_array) > 10
FOR_ALL()	Evaluates all values of an array to determine if all meet the filter criteria. In the example, the FOR_ALL() operator would return any rows of the column col_int_array where all values are greater than 10. For details, see Array FiltersArray Filters.	FOR_ALL(col_int_array) > 10 FOR_ALL(col_int_array) = 1
[ NOT ] IN	Equal to one of multiple possible values	DeptCode IN (101, 103, 209)
IS [ NOT ] NULL	Compare to NULL (missing data)	Address IS NOT NULL
IS [ NOT ] DISTINCT FROM	Compares the equality of two expressions for a Boolean result, including comparisons that involve NULL values. This operator ensures reliable comparisons in scenarios where NULL values need to be treated as significant data points. Normally, NULL = NULL evaluates to false because NULL represents an unknown value. However, the statement a IS NOT DISTINCT FROM b treats NULL as a comparable value and returns true when both values are NULL or when a = b. Inversely, a IS DISTINCT FROM b returns true if the values are different or if one value is NULL while the other is not NULL.	Debt IS NOT DISTINCT FROM Receivables

EXISTS

An EXISTS clause used in a WHERE statement evaluates if a subquery returns any rows.

For each row that the database computes in the outer query, if the subquery returns at least one row, the EXISTS clause evaluates to true. In this outcome, the outer query returns its row.

If the subquery returns zero rows, the EXISTS clause evaluates to false, and the outer query excludes those rows.

The NOT EXISTS clause performs the opposite Boolean logic. In this case, the outer query returns rows only when the subquery has no matches.

Examples

These examples use two tables, one for company departments and the other for employees who are assigned to those departments.

Create the departments table for department data.

CREATE TABLE departments (
    id INT,
    name VARCHAR(50)
);

Create the employees table for employee data.

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department_id INT
);

Insert department data for three departments, HR, IT, and Marketing, into the departments table.

INSERT INTO departments (id, name) VALUES
    (1, 'HR'),
    (2, 'IT'),
    (3, 'Marketing');

Insert employee data for four employees, Alice, Bob, Charlie, and David, into the employees table.

INSERT INTO employees (id, name, department_id) VALUES
    (1, 'Alice', 1),
    (2, 'Bob', 2),
    (3, 'Charlie', 2),
    (4, 'David', 4);

Find Employees Belonging to an Existing Department

This example uses an EXISTS clause to find any names from the employees table who are assigned to a department listed in the departments table.

SELECT name
FROM employees AS e
WHERE EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);

The query returns all employees except David, who is assigned to a department that does not exist in the departments table.

Output

Bob 
Charlie
Alice

Find Departments That Have Employees

This query returns departments that have at least one employee. The subquery uses SELECT 1 to check whether any matching rows exist in the employees table. The output is the same if the query uses SELECT * instead.

SELECT name 
FROM departments AS d
WHERE EXISTS (
    SELECT 1 
    FROM employees AS e 
    WHERE e.department_id = d.id
);

The query results exclude the Marketing department because no employees belong to it.

Output

HR        
IT

Filter Departments Based on an Uncorrelated Table

In this example, the outer query filters the departments table where id != 3 (excluding the Marketing department). The subquery checks if there is at least one employee with a department identifier less than 4.

The query is uncorrelated because the subquery does not reference the departments table.

SELECT * 
FROM departments d
WHERE d.id != 3 
AND EXISTS (
    SELECT * 
    FROM employees e 
    WHERE e.department_id < 4
);

Output

HR        
IT

Find Employees Without a Valid Department

This example uses a NOT EXISTS clause. The example returns only employees who are assigned to a department identifier department_id not listed in the departments table.

SELECT name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);

Output: David

SIMILAR TO Operator

SIMILAR TO is a keyword that extends the LIKE operator, adding more features for match filtering including many metacharacters used in regular expressions.

% and _ both act as wildcard operators, but other supported metacharacters match traditional regular expressions.

Syntax

WHERE string1 SIMILAR TO string2

This table describes the metacharacters supported by the SIMILAR TO keyword.

Metacharacter	Description
%	Repeated wildcard, matches any characters zero or more times. Equivalent usage to LIKE.
_	Wildcard, matches exactly one character. Equivalent usage to LIKE.
\|	Alteration, meaning either of two alternatives (a\|b represents a or b).
*	Repetition of zero or more times.
+	Repetition of one or more times.
?	Repetition of zero or one times.
{m}	Repetition of exactly m times.
{m,n}	Repetition between m and n times (inclusive).
{m,}	Repetition of at least m times.
()	Logical grouping.
[]	A character class, equivalent to character classes in regular expressions. Example: [a-z] is any lowercase English letter.
^	Beginning of the line anchor and the negation in character classes.
$	End of the line anchor.

SIMILAR TO also supports escaping these metacharacters with \ and certain escape sequences supported by C family languages.

Metacharacter	Description
\a	The alert/bell character.
\b	The backspace character.
\B	A single \ character. Equivalent to \\.
\cX	The character with the same five lower order bits as X with all zeros as higher order bits.
\e	The character with the numeric value 27, Esc.
\f	Form feed.
\n	Newline.
\r	Carriage return.
\t	Horizontal tab.
\uwxyz	wxyz represents four hexadecimal digits, including two characters: one with numeric value wx, and one with numeric value yz.
\Uabcdefgh	abcdefgh represents eight hexadecimal digits, including four characters: numeric value ab, numeric value cd, numeric value ef, and numeric value gh.
\v	Vertical tab.
\xd....	d is any number of hexadecimal digits. If the number of digits is odd, a 0 is added to the left of the sequence. Numeric characters with values represented by every group of two symbols from d.
\0	NULL character 0.
\xy	xy are octal digits that represent the character with the numeric value xy.
\xyz	xyz are octal digits that represent the character with the numeric value xyz. If x is greater than or equal to 4, the database returns two characters: the numeric value 1 and the numeric value of the lower 8 bits of xyz.
\d	Matches any digit.
\s	Matches any whitespace.
\w	Matches any alphanumeric character or _.
\D	Matches any non-digit.
\S	Matches any non-whitespace.
\W	Matches any non-alphanumeric that is also not _.
\A	The beginning of a line. Equivalent to ^.
\m, \M, or \y	The boundary of a word. Equivalent to \b in normal regular expressions.
\Y	Any position that is not the boundary of a word.
\Z	The end of a line. Equivalent to $ in normal regular expressions.
\*	The star character.
\+	The plus character.
\?	The question mark character.

The database ignores any \ metacharacter that does not precede a metacharacter (including \) or an escape sequence in the metacharacter table.

Invalid expressions (unpreceded quantifiers, unclosed parentheses, etc.) result in query errors.

The database does not recognize the . character as a metacharacter, unlike the behavior in traditional regular expressions.

The database applies escape metacharacters that apply to string literals. To match a string containing a single '\', you might need to use '\\\\'.

Examples

'abc' SIMILAR TO 'abc'          true
'abc' SIMILAR TO 'a'            false
'abc' SIMILAR TO '%(b|d)%'      true
'abc' SIMILAR TO '(b|c)%'       false
'-abc-' SIMILAR TO '%\mabc\M%'  true
'xabcy' SIMILAR TO '%\mabc\M%'  false

Array Filters

A filter expression can use the functions FOR_SOME() and FOR_ALL() to apply a predicate against all values of an input array.

Array filter functions have the following rules:

They can only evaluate array types.

They can go on either the left or right side of a boolean comparison expression, but not both sides at the same time.

They must directly use a boolean comparison operator.

They cannot use the SOME and ALL SQL keywords.

Filter Behavior with Empty Arrays

Array filter functions have unique behavior when evaluating empty arrays.

FOR_ALL() evaluates an empty array as TRUE.

FOR_SOME() evaluates an empty array as FALSE.

If empty arrays must be evaluated for a different result, you can use the ARRAY_LENGTH function to specify a minimum array length. For example, this statement would evaluate an array as TRUE only if it is not empty and all values matched %ocient%:

ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%

For details about array functions, see the Array Functions and OperatorsArray Functions and Operators page.

Filter Behavior with NULL rows

Filtering with array functions can have different outcomes depending on whether the array row is NULL or if the values contained in the array are NULL.

If FOR_SOME() or FOR_ALL() evaluate a NULL row (the row itself is NULL, not that the array contains NULL values), then the result is always FALSE.

To check an array for the presence of NULL values, you must use the IS NULL operator. For example:

FOR_SOME(array_col) IS NULL

Array comparison operators, such as @>, <@, and && do not adhere to Boolean logic for NULL values. For details, see the Array Functions and OperatorsArray Functions and Operators page.

GROUP BY

Groups rows with the same values into summary rows, based on a specified aggregate function.

Syntax

SELECT <select_list_clause> FROM table_name 
    GROUP BY { column_name | expression | integer } [ , ... ]
    [ HAVING <filter_condition> ]

Parameters

Parameter	Description
<select_list_clause>	A list of one or more columns for the SELECT query. To use GROUP BY to summarize the query result set, at least one of the columns in column_list must include an aggregate function, such as SUM(), COUNT(), or MAX(). For a list of supported functions, see the Aggregate FunctionsAggregate Functions page.
table_name	The table to use for the query.
column_name	The name of one or more columns to use to group the result set. If you specify multiple columns, the result set is grouped by each unique combination of values from the columns.
expression	Any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.
integer	An integer representing the position of the columns referenced in the column_list. The first position starts at 1.
<filter_condition>	A HAVING clause that filters the aggregated groups based on a specified condition. For details, see the <filter_condition><filter_condition> section.

Example

This example uses a movie database to calculate the total amount spent on movie production per year.

SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
ORDER BY 1 ASC;

Output

year(release_date)	sum(budget)
1968	10500000
1979	31500000
1981	18000000
1982	28000000
1992	14000000
1997	200000000
2003	264000000
2009	237000000
2010	350000000
2012	670000000
2013	350000000
2015	414000000

HAVING

Filters aggregated rows based on a specified condition.

HAVING operates in a GROUP BY statement by setting a filter for the rows to be aggregated and grouped.

For information on using GROUP BY in a query statement, see GROUP BYGROUP BY.

Syntax

GROUP BY column_name HAVING <filter_condition>

Parameters

Parameter	Description
column_name	A column used for grouping that is evaluated by the filter_condition.
<filter_condition>	A logical combination of boolean predicates. For information on supported logical predicates, see <filter_condition><filter_condition>.

Example

This example calculates the total amount spent on movie production per year. The HAVING clause filters out any rows that do not have a sum of at least $100 million.

SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
HAVING SUM(budget) > 100000000
ORDER BY 1 ASC;

Output

year(release_date)	sum(budget)
1997	200000000
2003	264000000
2009	237000000
2010	350000000
2012	670000000
2013	350000000
2015	414000000

ORDER BY

Sorts the result set in ascending or descending order based on one or more specified columns.

If you specify multiple columns, they are sorted hierarchically from left to right.

Syntax

ORDER BY { column_position | column_name } [ ASC | DESC ] 
    [ NULLS FIRST | NULLS LAST ] [ , ... ]

Parameters

Parameter	Data Type	Description
column_position	Integer	The position of a column to be used for the ORDER BY sorting. Column positions start at 1.
column_name	String	The name of a column to be used for the ORDER BY sorting.
ASC \| DESC	String	Optional. Specifies whether to sort the column in ascending (ASC) or descending (DESC) order. If unspecified, defaults to ASC.
NULLS FIRST \| NULLS LAST	String	Optional. NULLS FIRST means null values go at the start of the result set. NULLS LAST means null values go at the end of the result set. If unspecified, defaults to NULLS FIRST.

Example

In this example, the ORDER BY statement orders the movies from newest to oldest.

SELECT release_date, title 
FROM movies 
ORDER BY release_date DESC;

Output

release_date	title
2015-06-17	Minnows
2015-06-09	Triassic World
2015-04-01	Fury 7
2013-11-27	Frigid
2013-04-18	Iron Chef 3
2012-10-25	Spyman 16
2012-07-16	Superhero 23
2012-04-25	The Tentpole
2010-06-30	The Last Airman
2010-06-16	Merchandise Vehicle 5
2009-12-10	CGI Why
2003-12-01	Swords & Scabbards
2003-10-10	Billy the Killy
2003-07-09	Pirates of Palm Springs
1997-11-18	Titania
1992-08-07	Bang Bang Western
1982-06-25	Blade Walker
1981-06-12	Raiders of the Jungle Temple
1979-08-15	Apocalypse None
1968-04-10	Space Odyssey 6000

LIMIT

Limits the number of returned rows from a query to a specified amount.

The returned rows are nondeterministic unless you use an ORDER BYORDER BY clause.

Syntax

<select_query> LIMIT limit_number

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
limit_number	The number of rows to return from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer.

Examples

Limit Returned Rows Using a Number

This example uses LIMIT to restrict the number of returned movie titles to only three.

SELECT title FROM movies LIMIT 3;

Output

Minnows
CGI Why
Billy the Killy

Limit Returned Rows Using an Expression

The LIMIT SQL statement can also accept expressions. This query uses the 1+2 expression with the sys.dummy table to create a table of three incrementing integers. For details, see Generate Tables Using sys.dummyGenerate Tables Using sys.dummy.

SELECT c1 FROM sys.dummy10 LIMIT 1+2;

Output

1
2
3

OFFSET

Skips a specified number of rows from the result set.

The returned rows are nondeterministic unless you use an ORDER BYORDER BY clause.

Syntax

<select_query> OFFSET offset_number

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
offset_number	The number of rows to skip when returning results from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer.

Examples

Skip Rows From the Result Set Using a Number

In this example, the ORDER BY statement orders the results chronologically. As a result, the OFFSET clause removes the oldest six movies from the result set.

SELECT release_date,
    title
FROM movies
ORDER BY release_date OFFSET 6;

Output

release_date	title
2003-07-09	Pirates of Palm Springs
2003-10-10	Billy the Killy
2003-12-01	Swords & Scabbards
2009-12-10	CGI Why
2010-06-16	Merchandise Vehicle 5
2010-06-30	The Last Airman
2012-04-25	The Tentpole
2012-07-16	Superhero 23
2012-10-25	Spyman 16
2013-04-18	Iron Chef 3
2013-11-27	Frigid
2015-04-01	Fury 7
2015-06-09	Triassic World
2015-06-17	Minnows

Skip Rows From the Result Set Using an Expression

The OFFSET SQL statement can also accept expressions. This query uses the expression 3+4 with the sys.dummy table to create a column of incrementing integers by skipping the first seven out of 10 rows. For details, see Generate Tables Using sys.dummyGenerate Tables Using sys.dummy.

SELECT c1 FROM sys.dummy10 OFFSET 3+4;

Output

8
9
10

INTERSECT

Returns any rows that match between two separate SELECT queries. To use INTERSECT, the two queries must be compatible, meaning they must return the same number of columns and have similar data types.

By default, the SQL statement eliminates duplicate rows unless the query includes the optional ALL keyword. Using the DISTINCT keyword is the same as this default behavior.

Syntax

<select_query1> INTERSECT [ ALL | DISTINCT ] <select_query2>

Parameters

Parameter	Description
<select_query1>	A valid SELECT query.
<select_query2>	A valid SELECT query.

Example

This example has two SELECT queries with an INTERSECT statement. The full query retrieves movies that had a budget of at least $200 million, but also earned more than $1 billion in revenues.

SELECT *
FROM movies
WHERE revenue > 1000000000
INTERSECT
SELECT *
FROM movies
WHERE budget > 20000000;

Output

movie_id	title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
597	Titania	200000000	100.025899	1997-11-18	1845034188	194	Released	7.5	7562
19995	CGI Why	237000000	150.437577	2009-12-10	2787965087	162	Released	7.2	11800
49026	Superhero 23	250000000	112.31295	2012-07-16	1084939099	165	Released	7.6	9106
10193	Merchandise Vehicle 5	200000000	59.995418	2010-06-16	1066969703	103	Released	7.6	4597
211672	Minnows	74000000	875.581305	2015-06-17	1156730962	91	Released	6.4	4571
122	Swords & Scabbards	94000000	123.630332	2003-12-01	1118888979	201	Released	8.1	8064
37724	Spyman 16	200000000	93.004993	2012-10-25	1108561013	143	Released	6.9	7604
109445	Frigid	150000000	165.125366	2013-11-27	1274219009	102	Released	7.3	5295
168259	Furious 7	190000000	102.322217	2015-04-01	1506249360	137	Released	7.3	4176
68721	Iron Chef 3	200000000	77.68208	2013-04-18	1215439994	130	Released	6.8	8806
135397	Triassic World	150000000	418.708552	2015-06-09	1513528810	124	Released	6.5	8662
24428	The Tentpole	220000000	144.448633	2012-04-25	1519557910	143	Released	7.4	11776

EXCEPT

The EXCEPT keyword returns the result set of a first SELECT query minus any matching rows from a second SELECT query.

EXCEPT requires the two queries to be compatible, meaning they must return the same number of columns and have similar data types. By default, the result set eliminates duplicate rows unless the query includes the optional ALL keyword. Using the DISTINCT keyword is the same as this default behavior.

The EXCEPT keyword can also be used to exclude specific columns from a SELECT * query. For information on that alternate usage, see the SELECTSELECT syntax and example.

Syntax

<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>

Parameters

Parameter	Description
<select_query1>	A valid SELECT query that is compared to select_query2. The results of an EXCEPT statement are the rows in select_query1 that do not match any row in select_query2.
<select_query2>	A valid SELECT query that is compared to select_query1.

Example

In this example, the first SELECT statement queries all rows in the movies table. The EXCEPT clause and the second SELECT statement eliminate from the results any rows with movies that had a budget greater than 20000000.

SELECT *
FROM movies
EXCEPT
SELECT *
FROM movies
WHERE budget > 20000000;

Output

title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
Bang Bang Western	14000000	37.380435	1992-08-07	159157447	131	Released	7.7	1113
Space Odyssey 6000	10500000	86.201184	1968-04-10	68700000	149	Released	7.9	2998
Raiders of the Jungle Temple	18000000	68.159596	1981-06-12	389925971	115	Released	7.7	3854

UNION

Returns the combined result set of two or more SELECT queries.

By default, UNION eliminates duplicate rows from the result set unless you specify the ALL keyword. Using the DISTINCT keyword is the same as this default behavior.

Syntax

<select_query1> UNION [ ALL | DISTINCT ] <select_query2> [ ... ]

Parameters

Parameter	Data Type	Description
select_query1	String	A valid SELECT query that is combined with select_query2.
select_query2	String	A valid SELECT query that is combined with select_query1.

Example

This example uses UNION to merge two separate queries for identical columns into the same result set.

SELECT title,
    popularity,
    vote_average
FROM movie
WHERE popularity > 800
UNION
SELECT title,
    popularity,
    vote_average
FROM movies.movie
WHERE vote_average > 7.5;

Output

title	popularity	vote_average
Raiders of the Jungle Temple	68.159596	7.7
Minnows	875.581305	6.4
Apocalypse None	49.973462	8
Superhero 23	112.31295	7.6
Billy the Killy	79.754966	7.7
Bang Bang Western	37.380435	7.7
Blade Walker	94.056131	7.9
Swords & Scabbards	123.630332	8.1
Merchandise Vehicle 5	59.995418	7.6
Space Odyssey 6000	86.201184	7.9

USING

Overrides various system configurations for processing a specified query.

The USING keyword is required for only the first query override, not for subsequent ones.

For descriptions of the supported query configurations, see the parameter table below.

Syntax

<select_query>
USING 
    [ SCHEDULING_PRIORITY = priority_value ] 
    [ MAX_ROWS_RETURNED = max_rows ]
    [ MAX_ELAPSED_TIME = max_elapsed_time ] 
    [ MAX_TEMP_DISK_USAGE = max_temp_disk_usage ] 
    [ CACHE_MAX_TIME = cache_max_time ]
    [ CACHE_MAX_BYTES = cache_max_bytes ]
    [ SERVICE CLASS service_class ]

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
priority_value	An optional clause that specifies the priority at which the query should run. The priority value is a floating point number, which indicates the priority compared to the other queries. The priority value does not specify any percentage of resources that the query takes or any specific degree of difference between queries. If unspecified, defaults to SCHEDULING_PRIORITY = 1.0.
max_rows	An optional clause that limits the number of rows returned by a query. If a query exceeds this number of rows, the system kills it. For example, max_rows_returned = 10 kills any queries returning more than ten rows.
max_elapsed_time	An optional clause that limits how long a query can run. If a query exceeds this time limit in seconds, the system kills it. For example, max_elapsed_time = 10 kills any queries taking longer than 10 seconds.
max_temp_disk_usage	An optional clause that limits the percentage of temporary disk used by a query. If a query exceeds this temporary disk limit, the system kills it. For example, max_temp_disk_usage = 10 kills any queries taking more than 10% of temporary disk space.
cache_max_time	An optional clause that affects whether a specified query uses results from the cache rather than executing the query. If there is a cached result with the same query text, executed less than cache_max_time seconds ago, the database uses that cached result. The default value is 0, and thus by default, the database does not return any cached results. The cache considers all SQL Nodes, and if a potential cached result is only available on a different SQL Node, the database redirects the query to that node. If you specify the force attribute on the connection, which disables load balancing and redirection, the database considers only cached results on the current node.
cache_max_bytes	An optional clause that controls whether the database stores the results of any specified query in the cache. The system stores the results of all queries executed using a service class specifying this attribute in the cache if the result size is smaller than this value. You can determine the size of a result set in bytes by querying the bytes_returned field of the completed_queries virtual table. The default value is 0, and thus by default, the database does not cache any results. The database caches results in memory on the SQL Nodes. These results are not cached if there is insufficient memory available.
service_class	An optional clause at the end of a query that sets a specific service class to run that query. For details, see CREATE SERVICE CLASSCREATE SERVICE CLASS.

Example

SELECT * FROM sys.dummy10 
USING 
    SCHEDULING_PRIORITY = 1.0 
    MAX_ROWS_RETURNED = 10
    MAX_ELAPSED_TIME = 10
    MAX_TEMP_DISK_USAGE = 10;

Output

c1         
-----------
1 
2 
3 
4 
5 
6 
7 
8 
9 
10

TRACE

An optional clause that executes the query, but discards the original result set. Instead, TRACE returns a result set of tracing data that describe the execution of the query.

Append the TRACE SQL statement to the end of a query. The statement can include optional parameters to control its frequency and level of detail.

To understand the results of this statement, see TRACE ResultsTRACE Results.

Trace queries have the same effect to the workload as a query run without the clause.

Contact Ocient Support for guidance in using the TRACE SQL statement.

Syntax

<select_query> TRACE 
    [ FREQUENCY frequency_int ] 
    [ RESOLUTION resolution_int ]

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
frequency_int	Controls how often the database samples trace events during query execution in milliseconds. If unspecified, defaults to 500 milliseconds.
resolution_int	Determines the level of detail captured for a trace or event. If the resolution of a trace is greater than the resolution of an event, the database records the event. If unspecified, defaults to 100 milliseconds.

TRACE Results

Each row of the trace output provides some information about what an operator instance has done in the time between the previous trace sample and the most current sample.

Column	Description
plan_parent_id	The UUID of the parent of the operator instance in the query plan. If an operator has multiple parents, this field specifies the UUID of one of the parents.
plan_op_id	The UUID of the operator in the query plan.
operator_type	The type of operator that the database uses to sample trace events.
node_id	The UUID of the node where this operator executes.
code_id	The identifier of the VM core where this operator instance executes. Core identifiers are unique for each node.
op_id	The identifier of the operator instance. Identifiers are unique for each node.
time	The time in milliseconds when the database collects the information for the trace event relative to the start of the query.
group	Group related trace events together.
sample	The sample of trace event data. For possible values, see the following table.
value	The value of trace event data.

Sample and Value Columns

The sample and value columns describe what occurred in a given trace period.

Sample	Description
ROWS_IN	The number of rows an operator instance has read from its children operators.
ROWS_OUT	The number of rows the operator instance returns.
BLOOM_FILTERED_ROWS	The number of rows that this operator instance discards by using Bloom filters.
SCHEDULE_CYCLE	The number of cycles in the schedule for this operator instance.
OOM_CYCLE	The number of out-of-memory cycles in the schedule for this operator.
INITIALIZE	The time when the database initializes the operator.
FINALIZE	The time when the database finalizes the operator.

Example

SELECT * FROM sys.dummy10 TRACE;

Output

plan_parent_id	plan_op_id	operator_type	node_id	core_id	op_id	group	sample	value
77041d2b-f7ca-49d5-a3f5-4b75b0782ed1	498690cf-4a9b-4c1f-affa-417f31fddc2d	RENAME_OPERATOR	08ca7b05-1e4d-455f-9f46-2174ed048d33	4	8441	xg::db::vm::operators::operatorTraceEvents	ROWS_IN	1
77041d2b-f7ca-49d5-a3f5-4b75b0782ed1	498690cf-4a9b-4c1f-affa-417f31fddc2d	RENAME_OPERATOR	08ca7b05-1e4d-455f-9f46-2174ed048d33	4	8441	xg::db::vm::operators::operatorTraceEvents	ROWS_OUT	1
77041d2b-f7ca-49d5-a3f5-4b75b0782ed1	498690cf-4a9b-4c1f-affa-417f31fddc2d	RENAME_OPERATOR	08ca7b05-1e4d-455f-9f46-2174ed048d33	4	8441	xg::db::vm::operators::operatorTraceEvents	BLOOM_FILTERED_ROWS	0

TAG

An optional clause that adds one or more tags to the SQL query. You can also add tags to a common table expression in the WITH clause. After you define tags, you can find them in the sys.queries and sys.completed_queries system catalog tables.

Syntax

<select_query> <tag> [ ... ]

<tag> ::= TAG <tag_identifier>

Parameters

Parameter	Description
<select_query>	A valid SELECT query.
tag_identifier	Identifier of the tag for the query. Enclose the identifier in double quotes if it contains any special characters, such as spaces.

Examples

SQL Query with One Tag

Select the count of a table with one row and tag the query with the name count1.

SELECT COUNT(*) FROM sys.dummy1 TAG count1;

SQL Query with Multiple Tags

Select the device model and tag this query with two names device_model and phones.

SELECT device_model 
FROM system.adtech_flat 
LIMIT 1 
TAG device_model 
TAG phones;

Common Table Expression with One Tag

Define a common table expression with the average_budget tag. You can also add another tag for the whole query overall_budget.

WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
    TAG average_budget
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget
TAG overall_budget;

String Literals and Escape Sequences

All string literals in SQL statements must be enclosed in single quotes. To use a single quote within a string, you can use another single quote as an escape, ''.

For other escape sequences, include an e character before the string literal, i.e., before the opening single quote. This directs the system to recognize escape sequences in the string literal. This means:

All single \ characters in the string now escape themselves.

Any subsequent character after the \ is also escaped if it matches an escape sequence (see the table).

If you use escape sequences, be prepared that you might need to alter strings that include \, such as directory paths.

Supported Escape Sequences

Escape Sequence	Description
''	Single quotation mark
\"	Double quotation mark
\n	Newline character
\r	Carriage return character
\f	Form feed character (i.e. page break)
\b	Backspace
\\	Backslash
\t	Tab

Examples

These examples show how the system interprets strings with and without escape sequences.

String Without an Escape Sequence

This example selects the simple string literal '\my\directory\path' that does not use escape sequences.

SELECT '\my\directory\path';

Output: \my\directory\path

String With an Escape Sequence

This example selects the simple string literal '\my\directory\path' and uses an escape sequence. The result omits the backslashes.

SELECT e'\my\directory\path';

Output: mydirectorypath

String With an Escape Sequence to Retain the Backslashes

This example uses the '\\my\\directory\\path' string with an escape sequence to include backslashes in the result.

SELECT e'\\my\\directory\\path';

Output: \my\directory\path

Escape Sequences with Regular Expressions

Be careful using escape sequences with strings that also use regular expressions. Escape sequences in SQL syntax override the escape sequences of regular expressions.

In this example, the REGEXP_SUBSTRREGEXP_SUBSTR function uses the regular expression \w+ to match any word characters.

SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz', 
    '\w+'
    );

Output: abcdefghijklmnopqrstuvwxyz

The function behaves differently if the regular expression is an escape sequence because it overrides the \ character. The regular expression searches only for the character w.

SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz', 
    e'\w+'
    );

Output: w

SQL ReferenceSQL Reference

Data Control Language (DCL) Statement ReferenceData Control Language (DCL) Statement Reference

Data Definition Language (DDL) Statement ReferenceData Definition Language (DDL) Statement Reference

test page Ocient

Ocient SQL Syntax

Default Schema

Querying SQL Statement Reference

WITH

SELECT

<select_list_entry>

<from_clause>

Using SELECT *

Using SELECT * EXCEPT

Using Lateral Column Aliases

FROM

JOIN

Types of Join Operations ( <join_operation> )

JOIN Type Descriptions

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

SEMI JOIN

ANTI JOIN

WHERE

<filter_condition>

EXISTS

SIMILAR TO Operator

Array Filters

GROUP BY

HAVING

ORDER BY

LIMIT

OFFSET

INTERSECT

EXCEPT

UNION

USING

TRACE

TRACE Results

TAG

String Literals and Escape Sequences

Related Links